Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BekahLoSurdo
Resolver IV
Resolver IV

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!

2 ACCEPTED SOLUTIONS

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] )
                )
            )
        )
    )

a1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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] )

Eaxample 3.PNG

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!

View solution in original post

3 REPLIES 3
BekahLoSurdo
Resolver IV
Resolver IV

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

Example 1.PNG

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 )

Example 2.PNG

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

 

Thanks again!

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] )
                )
            )
        )
    )

a1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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] )

Eaxample 3.PNG

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.