Forum Discussion

BekahLoSurdo's avatar
BekahLoSurdo
Resolver IV
7 years ago
Solved

DAX Table Function With Multiple Filters

Hi experts, I need help creating a table function that transforms a singular source table based on a hierarchy of rules (each "rule" is based on a separate column). I'm new to DAX so I'm not sure if...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi BekahLoSurdo 

    You may create measures and use table visual to get the table.For example:

    Count_date = CALCULATE(DISTINCTCOUNT(Table1[Date]),ALLEXCEPT(Table1,Table1[ID]))
    Measure =
VAR a =
    CALCULATETABLE (
        VALUES ( Table1[Version] ),
        ALLEXCEPT ( Table1, Table1[ID], Table1[Date] )
    )
RETURN
    IF (
        [Count_date] >= 2,
        CALCULATE ( SUM ( Table1[Amount] ), FILTER ( Table1, Table1[Rank_date] = 1 ) ),
        IF (
            "Final" IN a,
            CALCULATE (
                SUM ( Table1[Amount] ),
                FILTER ( Table1, Table1[Version] = "Final" )
            ),
            IF (
                "Revision" IN a,
                CALCULATE (
                    SUM ( Table1[Amount] ),
                    FILTER ( Table1, Table1[Version] = "Revision" )
                ),
                CALCULATE (
                    MAX ( Table1[Amount] ),
                    ALLEXCEPT ( Table1, Table1[ID], Table1[Date] )
                )
            )
        )
    )

    Regards,

  • BekahLoSurdo's avatar
    BekahLoSurdo
    7 years ago

    Hi v-cherch-msft 

    Thank you for all of your help! This wasn't exactly what I needed (I really needed a table for future modelling processes and I realize now that I also needed to account for more than one "Final" or "Revision" version) but I was able to use your great ideas to figure it out. 

     

    Here is the final DAX code:

    In the original table I created two columns - 

     

    MostRecentDate = CALCULATE( LASTDATE( Tables[Date] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ) )
    MostRecentAmount = 
VAR FinalCount = CALCULATE( COUNTROWS( Tables ),
    FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ),
    FILTER( Tables, Tables[Version] = "Final" ),
    FILTER( Tables, Tables[Date] = Tables[MostRecentDate] )
)
VAR RevisionCount = CALCULATE( COUNTROWS( Tables ),
    FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ),
    FILTER( Tables, Tables[Version] = "Revision" ),
    FILTER( Tables, Tables[Date] = Tables[MostRecentDate] )
)
RETURN IF( FinalCount > 0,
    CALCULATE( MAX( Tables[Amount] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ), FILTER( Tables, Tables[Date] = Tables[MostRecentDate] ), FILTER( Tables, Tables[Version] = "Final" ) ),
    IF( RevisionCount > 0,
        CALCULATE( MAX( Tables[Amount] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ), FILTER( Tables, Tables[Date] = Tables[MostRecentDate] ), FILTER( Tables, Tables[Version] = "Revision" ) ),
        CALCULATE( MAX( Tables[Amount] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ), FILTER( Tables, Tables[Date] = Tables[MostRecentDate] ) )
    )
)

    Then I created my new table as - 

     

    ProposalHistory_MostRecent = 
    SUMMARIZECOLUMNS( Tables[ID],
        Tables[MostRecentDate],
        Tables[MostRecentAmount] )

    Please let me know if you think it can be done with any more efficiency given the new information. If I don't hear from you, I will mark these both as solutions as I think they could each help others (given different requirements).

     

    Thanks again!

BekahLoSurdo's avatar
BekahLoSurdo
Resolver IV
7 years ago

I was able to do this with the creation of two helper columns and a new table, is there a better way?

RuleHierarchy = VAR MaxDate = CALCULATE( MAXX( Tables, Tables[Date] ), ALLEXCEPT( Tables, Tables[ID] ) )
    VAR MinDate = CALCULATE( MINX( Tables, Tables[Date] ), ALLEXCEPT( Tables, Tables[ID] ) )
    VAR ValidVersion = IF( Tables[Version] = "Revision" || Tables[Version] = "Final", Tables[Version], BLANK() )
    VAR MaxAmount = CALCULATE( MAX( Tables[Amount] ), ALLEXCEPT( Tables, Tables[ID] ) )
    RETURN IF( MaxDate <> MinDate && MaxDate = Tables[Date], 1, 
        IF( ValidVersion = "Final", 2,
            IF( ValidVersion = "Revision", 3,
                IF( Tables[Amount] = MaxAmount, 4 ) ) ) )
UseLine = IF( CALCULATE( MIN( Tables[RuleHierarchy] ), ALLEXCEPT( Tables, Tables[ID] ) ) = Tables[RuleHierarchy], 1, 0 )

It seems like there is a less convoluted way to get there...

 

Thanks again!

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi BekahLoSurdo 

    You may create measures and use table visual to get the table.For example:

    Count_date = CALCULATE(DISTINCTCOUNT(Table1[Date]),ALLEXCEPT(Table1,Table1[ID]))
    Measure =
VAR a =
    CALCULATETABLE (
        VALUES ( Table1[Version] ),
        ALLEXCEPT ( Table1, Table1[ID], Table1[Date] )
    )
RETURN
    IF (
        [Count_date] >= 2,
        CALCULATE ( SUM ( Table1[Amount] ), FILTER ( Table1, Table1[Rank_date] = 1 ) ),
        IF (
            "Final" IN a,
            CALCULATE (
                SUM ( Table1[Amount] ),
                FILTER ( Table1, Table1[Version] = "Final" )
            ),
            IF (
                "Revision" IN a,
                CALCULATE (
                    SUM ( Table1[Amount] ),
                    FILTER ( Table1, Table1[Version] = "Revision" )
                ),
                CALCULATE (
                    MAX ( Table1[Amount] ),
                    ALLEXCEPT ( Table1, Table1[ID], Table1[Date] )
                )
            )
        )
    )

    Regards,

    • BekahLoSurdo's avatar
      BekahLoSurdo
      Resolver IV
      7 years ago

      Hi v-cherch-msft 

      Thank you for all of your help! This wasn't exactly what I needed (I really needed a table for future modelling processes and I realize now that I also needed to account for more than one "Final" or "Revision" version) but I was able to use your great ideas to figure it out. 

       

      Here is the final DAX code:

      In the original table I created two columns - 

       

      MostRecentDate = CALCULATE( LASTDATE( Tables[Date] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ) )
      MostRecentAmount = 
VAR FinalCount = CALCULATE( COUNTROWS( Tables ),
    FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ),
    FILTER( Tables, Tables[Version] = "Final" ),
    FILTER( Tables, Tables[Date] = Tables[MostRecentDate] )
)
VAR RevisionCount = CALCULATE( COUNTROWS( Tables ),
    FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ),
    FILTER( Tables, Tables[Version] = "Revision" ),
    FILTER( Tables, Tables[Date] = Tables[MostRecentDate] )
)
RETURN IF( FinalCount > 0,
    CALCULATE( MAX( Tables[Amount] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ), FILTER( Tables, Tables[Date] = Tables[MostRecentDate] ), FILTER( Tables, Tables[Version] = "Final" ) ),
    IF( RevisionCount > 0,
        CALCULATE( MAX( Tables[Amount] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ), FILTER( Tables, Tables[Date] = Tables[MostRecentDate] ), FILTER( Tables, Tables[Version] = "Revision" ) ),
        CALCULATE( MAX( Tables[Amount] ), FILTER( Tables, Tables[ID] = EARLIER( Tables[ID] ) ), FILTER( Tables, Tables[Date] = Tables[MostRecentDate] ) )
    )
)

      Then I created my new table as - 

       

      ProposalHistory_MostRecent = 
    SUMMARIZECOLUMNS( Tables[ID],
        Tables[MostRecentDate],
        Tables[MostRecentAmount] )

      Please let me know if you think it can be done with any more efficiency given the new information. If I don't hear from you, I will mark these both as solutions as I think they could each help others (given different requirements).

       

      Thanks again!