Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX measures , same logic , different results

Hello community,

 

I created the Diff-1_Pallets and Diff-2_Pallets measures , in order to calculate the differences between the latest extraction set vs the previous one, and between the latest extraction set vs the oldest one , respectively.

 

The Shipments[Index] column shows the extraction dates' ranking, 1 being the latest extraction and 3 the oldest one.

 

Diff-1_Pallets =
VAR LatestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = 1))
VAR PreviousExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = 2))
Return
LatestExtraction - PreviousExtraction

 

Diff-2_Pallets =
VAR LatestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = 1))
VAR OldestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = 3))
Return
LatestExtraction - OldestExtraction
 
These two measures return the correct results.
 
 
I then decided to experiment a little bit. So, instead of hardcoding Shipments[Index] = 1, etc, I created variables instead.
The new measures are below.
 
 
Diff Vs Previous =
VAR CurrentDataSet = MIN(Shipments[Index])
VAR PreviousDataSet = CurrentDataSet + 1
VAR LatestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = CurrentDataSet))
VAR PreviousExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = PreviousDataSet))
Return
LatestExtraction - PreviousExtraction
 
Diff Vs Oldest =
VAR CurrentDataSet = MIN(Shipments[Index])
VAR OldestDataSet = MAX(Shipments[Index])
VAR LatestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = CurrentDataSet))
VAR OldestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = OldestDataSet))
Return
LatestExtraction - OldestExtraction
 
The screenshot below has a questionmark icon next to the wrong calculations of the second set of measures.
 
My question is why do the Diff Vs Previous and Diff Vs Oldest measures return wrong results, from the moment that they share the same logic with the first set of measures.
 

 

Kind regards,

 

George

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Anonymous.

    Any update for these? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous,

    Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to troubleshoot.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Anonymous 

     

    Thank you for your reply.

     

    Below you can find a shorter version of the Shipments table and a screenshot of my data model.

     

    The screenshot found at my initial message shows the results filtered to the status RU.

     

    IndexExtraction DateStatusShipment dateSum of Pallet_Num

    115/11/2021RU04/11/20216
    115/11/2021RU03/01/20224
    115/11/2021RU17/01/20224
    115/11/2021RU25/04/20224
    202/11/2021RU05/11/20216
    202/11/2021RU10/01/20224
    202/11/2021RU31/01/20224
    326/10/2021RU02/11/20216
    326/10/2021RU10/01/20224
    326/10/2021RU31/01/20224

     

     

     

    Hope this helps.

     

    George

     

    • m3tr01d's avatar
      m3tr01d
      Continued Contributor
      4 years ago

      Anonymous 
      The only difference is with the field PreviousDataSet  and OldestDataset.

      I would build two tables using Week + PreviousDataset and Week + OldestDataset. Then, you can compare where the difference is.

      When you filter the Week from the Date table, it will filter the Shipment table. So maybe, when the rows are filtered, sometimes, you will have 3 dates with 3 index so in this case MAX( Index) and Current+1 won't return the same value. 
      If Current is 1 then MAX( Index) will return 3 and the other one 2.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        thank you m3tr01d  and apologies for my late reply, will do that and get back to you

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      HI Anonymous,

      If these expressions get the same result when you use static values and get different results when you add aggregate functions to let them dynamically based on current row contents.
      For this scenario, it means these functions are working on the different row context levels so that the min/max function got the different results and they affect expressions calculations.

      Please take a look at the following blog about row and filter context if help:

      Row Context and Filter Context in DAX - SQLBI
      Regards,

      Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        thank you Xiaoxin , will have a look at the link for sure

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Anonymous.

    Any update for these? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng