Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Convert Tableau LOD Nested Fixed function to Power BI DAX

Need help with replicating a Nested 'FIXED' Level of detail expression in Tableau to DAX-

Top Duration(mins) = { FIXED [Device]:MAX(
{ FIXED [Device],[Reason]:SUM(IF [State]='Off' THEN [Duration(mins)] END)})}
Basically find the maximum Duration and corresponding Reason for every Device when State = Off. 

Sample Data   
Mfg plantDeviceReasonDuration (mins)
TXL1Waiting10
TXL1Waiting for pickup20
TXL1Line Change30
TXL2Filler40
TXL2Line Change50
TXL3Waiting60
TXL3Problem with line70
TXL4Cleanup80
TXL4Problem with line90
TXL4Waiting for pickup100
    
Expected Results  
Mfg PlantDeviceTop ReasonTop Duration(mins)
TXL1Line Change30
TXL2Line Change50
TXL3Problem with Line70
TXL4Waiting for pickup100
Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous I don't see [State] in your data but best guess would be one of these two variants:

    Top Duration(mins) = MAXX(FILTER('Table',[State]="Off"),[Duration(mins)]

or

Top Duration(mins) = CALCULATE(MAX('Table'[Duration(mins)]),'Table'[State]="Off")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Greg_Deckler Sorry, should have mentioned that the lowest level of detail in the data is a key value (Event_key). So we need to do a SQL 'group by' kind of DAX measure that SUMS up Duration for every Device & Reason and then gets the MAX for a each Device. I've tried ALLEXCEPT and SUMMARIZE but cant get it to work. 


      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        5 years ago

        Anonymous Have you tried adding a new column to your table with my suggestion? Your latest reply says you need to SUM duration? What does the original raw data actually look like? From your sample there is no summing required. If you have more granular rows than you have posted, my measure can be adapted, but we need all the info please. 

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    Anonymous  I am assuming you also have a [State] column in your data? 

     

    One option is to do this as a new column: 

     

    LastDuration = 

    IF(
    COUNTROWS(FILTER('Table', 'Table'[Duration] >=EARLIER('Table'[Duration]) && 'Table'[State] = "Off" && 'Table'[Device] = EARLIER('Table'[Device]))) = 1, 1, 0)
     
    Then use that as a filter in your visuals.
  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Just based on the sample data you have provided, you can use the following calculated table:

    Table 2 = SUMMARIZE('Table','Table'[Mfg plant],'Table'[Device],"Top Reason",CALCULATE(MAX('Table'[Reason]),FILTER('Table','Table'[Duration (mins)] = MAX('Table'[Duration (mins)]))),"Top Duration(mins)",MAX('Table'[Duration (mins)]))

     

    Would you please explain more about [State] column or provide raw data for accurate help.

     

    Best Regards,

    Dedmon Dai