Forum Discussion
Running Total and Null Values
Hi there.
You are getting "strange" results because you are not following Best Practices of dimensional design - easy as that.
If you run this in DaxStudio:
DEFINE MEASURE Conccentrator_Final[MTD2] = CALCULATE ( [BTZ RMO], DATESMTD( 'Calendar'[Date] ), Conccentrator_Final[Feed Oz's_2] > 0 ) EVALUATE CALCULATETABLE ( ADDCOLUMNS ( VALUES ( 'Calendar'[Date] ), "RMO", ROUND ( [BTZ RMO], 0 ), "MTD", ROUND ( [BTZ RMO MTD], 0 ), "MTD2", Round( [MTD2], 0 ) ), 'Calendar'[YYYY-MM] = "2020-02" )you'll see that [MTD2] gives you the correct answer. The reason being, [MTD2] uses Calendar to do any time-intel calculations. Such calculations do work correctly ONLY in case you are applying the functions to a proper Date table in the model. Any other column and you're out of luck.
You should strictly follow these golden rules if you want to stay on the safe side:
1. Slicing is only ever done through dimensions.
2. Fact tables' columns are all hidden without exceptions. Only measures can be visible.
3. Time-intel is applied only to proper Date tables.
4. The design must be either a star or a flake schema. Any deviation from this and you're asking for troubles.
6. Bi-directional filtering is used ONLY when strictly necessary. 99% of filtering is one-way only.
Best
D
Hi there.
You are getting "strange" results because you are not following Best Practices of dimensional design - easy as that.
If you run this in DaxStudio:
DEFINE
MEASURE Conccentrator_Final[MTD2] =
CALCULATE (
[BTZ RMO],
DATESMTD( 'Calendar'[Date] ),
Conccentrator_Final[Feed Oz's_2] > 0
)
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Calendar'[Date] ),
"RMO", ROUND ( [BTZ RMO], 0 ),
"MTD", ROUND ( [BTZ RMO MTD], 0 ),
"MTD2", Round( [MTD2], 0 )
),
'Calendar'[YYYY-MM] = "2020-02"
)
you'll see that [MTD2] gives you the correct answer. The reason being, [MTD2] uses Calendar to do any time-intel calculations. Such calculations do work correctly ONLY in case you are applying the functions to a proper Date table in the model. Any other column and you're out of luck.
You should strictly follow these golden rules if you want to stay on the safe side:
1. Slicing is only ever done through dimensions.
2. Fact tables' columns are all hidden without exceptions. Only measures can be visible.
3. Time-intel is applied only to proper Date tables.
4. The design must be either a star or a flake schema. Any deviation from this and you're asking for troubles.
6. Bi-directional filtering is used ONLY when strictly necessary. 99% of filtering is one-way only.
Best
D
Anonymous Thanks for the golden rules. I kept seeing references to DaxStudio in other posts without really understanding it's power, thanks to your answer I was motivated to finally look into it. Being able to "see" a Dax created table is a HUGE help for a beginner like myself. I found this great guide from Matt Allington (https://exceleratorbi.com.au/getting-started-dax-studio/ ) are there other basic guides or forums that you would recommend that pertain to DaxStudio itself?
Your comment regarding time intel functions and a proper calendar table make sense. Now the issue is a simpler one....because I'm graphing the data, how do I display null values for any date in the future. See the table below for March. I don't want any values beyond "today" (3/4/20) to be displayed on the graph.
- Here are all the techniques to hide future periods:
https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/
As for DaxStudio... I don't know any materials. You can try to use the Help section in DaxStudio itself on the ribbon.
Best
DThanks for the resource, that solved the issue.
