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 this can all be done in one table function or if I need to create helper columns in the source table first.

 

Sample Source:

IDDateVersionAmount
ABC1/1/2019Initial100
ABC2/1/2019Final200
DEF1/1/2019Initial150
DEF1/1/2019Revision300
DEF1/1/2019Final450
GHI2/1/2019 600
GHI2/1/2019Initial700
JKL2/1/2019Initial200
JKL2/1/2019Revision300
MNO1/1/2019Initial400
MNO1/1/2019Revision250
MNO2/1/2019Initial600
PQR1/1/2019Initial200
PQR1/1/2019Data Call300

 

For each distinct ID, the output should show a Date/Amount combination chosen as follows:

 

- If there are multiple Dates, choose the most recent with its corresponding Amount

- Else if there are multiple Versions, choose the "Final" Amount first; if not available, choose "Revision"

- Else choose the highest Amount (and its corresponding Date)

*In all cases, the outputted Date/Amount combination should be from the same line of the original table, i.e. these should not be computed separately but rather the rules should dictate which row from the original table is kept*

 

Desired Outcome:

IDDateAmount
ABC2/1/2019200
DEF1/1/2019450
GHI2/1/2019700
JKL2/1/2019300
MNO2/1/2019600
PQR1/1/2019300

 

Thank you in advance!

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!

3 Replies

  • 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!