DAX & DATA MODELING Rules & Tricks www.goodly.co.

in

5 Thumb Rules for DAX Measures • Always use a separate Date table in your data model. Mark it as a Date Table

• Hide columns that are needed but are irrelevant for the user. Quick Tip: Use
No Implicit Measures – Meaning, dragging a field (column) into values area of Relationships view to hide multiple columns: use SHIFT + DOWN ARROW for
the pivot to get Sum, Count etc... (that’s illegal). You shouldn’t do that in DAX, selection of columns >> Right click menu >> Hide
instead for every SUM, COUNT or AVERAGE to be calculated, write a DAX
formula i.e. create an Explicit Measure. Implicit measures won’t take you far and • Create a Blank Table and assign all Measures to that Table. This way your
measures won’t be scattered all over the model
won’t perform sophisticated calculations

No Calculated Columns – Whenever in doubt, decide NOT to make a • Use the new model diagram to break-out complex models by subject area in
calculated column instead create a Measure. Calculated columns are legal separate diagrams
when you want the values on rows, columns, slicer or horizontal axis of the
• Use DIVIDE() function to prevent division by 0, and to improve the speed of your
chart etc.. Calculated Columns add major bulk to your data model divisions
No Naked Columns in a measure – Writing the following DAX =Data[Units]
• Use standard abbreviations like YTD, LY, PY, PP as suffix, to keep the base fields
makes no sense (and will result in an error), because it doesn’t know what to together
do with the entire Units column. Always* use an aggregator around the
column. This will work =SUM( Data[Units] ) • Use double backslash // to write comments in a row, in between your DAX code

Measure Writing Conventions – While writing DAX it’s a best practice to • Some important operators and their explanation
• Precede column name with table name in square brackets. Data[Category]
• Measures to be written without table name in square brackets. [Total Sales] Operator Meaning Example
&& (double Creates an AND condition Cal [Year] = 2019 &&
Generating a Scalar Value – This is super important and you’ll often run into ampersand) between two expressions Cal [Month] =“Jan”
errors because your measure isn’t returning a scalar value in other words a || (double pipe Creates an OR condition Sales [Region]= “North") ||
single value. Your measures have to return a single value else they’ll throw up symbol) between two expressions Product [Colour] =“Red”
an error IN Creates multiple OR Product [Category] IN
conditions { “Low", “Mid", “Premium" }
*There can be a possibility of writing naked columns in row context in a row iterator function like SUMX. For
instance =SUMX( Data, Data[Units] * 5) will multiply each row’s unit to 5 and will then take the SUM. Two
worthy things to notice. 1. Data[Units] column is naked and is working under row context 2. There is no point
of this calculation, it’s just a demo. I couldn't come up something meaningful promptly 
HOW DAX MEASURES ARE EVALUATED RELATIONSHIP TIPS in power bi (mind it ☺) www.goodly.co.in

Between the DAX Code and it’s result is a Black Box. You need to understand these 3 steps What are Relationships
thoroughly to get your head around what goes inside that box
• Relationships are like virtual VLOOKUPs – A VLOOKUP without actually performing it
Consider this Data and Pivot
• Create relationship between 2 tables – In the Relationship Tab of Power Pivot or Power BI simply
Let’s talk about how the drag the common column from the transactions table (the data-set where you thought of writing
total sales for Bangalore a VLOOKUP) and link it to the matching column in lookup table (source data for VLOOKUP)
is calculated
Relationship Pitfalls

1. Always Always Always create the relationship from the transactions table to the lookup table
and NOT the opposite way

Step 1 : The filter context from the pivot / visual plus the CALCULATE function filter context is 2. Most times Power BI will create the relationship automatically, always recheck them manually
applied to the data. For Total Sales (5,308) the filter context is Region = Bangalore
3. At times you’ll have the need to relate one column (of the lookup table) to multiple columns (of
the transactions table) Sure enough… you can do that but there can only be one active
relationship between 2 tables. The second relationship that you create will be inactive (with a
dotted line). The dotted line marks the inactive relationship
Region is filtered to Bangalore.
4. The inactive relationship can be made active in measures using USERELATIONSHIP function.
Note that filter context can also come from the
lookup table and or the CALCULATE function USERRELATIONSHIP (<column1>,<column2>), this can be used in the second argument of the
CALCULATE function

Step 2 : On the filtered data, the DAX calculation is carried out. In our case the DAX Measure for 5. Duplicates are not allowed in the lookup table (both in Power Pivot and in Power BI). You’ll get
Total Sales = SUM ( Data[Sales] ) an error, if while creating the relationship there are duplicates in the lookup table

Step 3 : The result of the DAX calculation is then returned to the visual / pivot. In our case the 6. If duplicates pop up after refreshing the data model – it would still result in an error and all
result for Bangalore Sales is 5,308. This process then repeats for all the coordinates in a pivot table / measures and filter propagation would stop working
visual. In our case there are 4 coordinates, Region = Bangalore, Region = Mumbai, Region = New
Delhi, Region = All (no filters) which is also the grand total 7. The following types of relationships are supported in Power BI/ Power Pivot
Words of Wisdom
• The above 3 steps happen under the hood and what you see is just the result. To be able to
write, edit and debug complex DAX measures you’ll have to master and mentally visualize how
these 3 steps are working on your data
• I have said it earlier.. I’ll say it again – your measure has to compulsorily return a scalar value
• For mastering DAX focus on understanding behavior of functions - What does it do, What does
it accept, What does it return and if there are any exceptions. Rest will fall in place with practice