Forum Discussion

troyhimes's avatar
troyhimes
Resolver I
6 years ago
Solved

Running Total and Null Values

Running Total and Null Values xlsx file 

 

None of the previous answers to previous questions regarding the same subject seemed to fit my circumstances.  I'm sure it is related to the filter context of my measures, but I don't see it.

 

The MTD values (BTZ RMO MTD) show up correctly when there is a null value for the day (BTZ RMO) in the first table, but as soon as a sum value of a separate calculated column (sum of MMP BTZ Oz MMP) is added the MTD values become null.  I require the 3rd column for a graph.  Any suggestions?  A side note...I don't fully understand why the individual daily values (BTZ RMO) aggregate correctly (Grand Total = 5492) doesn't match the individual sums (120+287+83+etc.....=5345)

 

Table 1

 

 Table 2

 

 

  • Anonymous's avatar
    Anonymous
    6 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • troyhimes's avatar
      troyhimes
      Resolver I
      6 years ago

      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.