Forum Discussion

thyrast's avatar
thyrast
New Member
3 years ago
Solved

DAX filter with multiple criteria

Hi, 

I want to filter based on two columns from a table, one containing text(this one I manage), and one filter finding the latest(highest) version(which is a text field, but to find the latest version I assume one must convert or add column to find the max value. 

This is my two tables, they are related by the ID column. I want to filter table 2 based on Type and Version in Table 1. 

Table 1:

IDTypeVersion
5Budget000001
4Prognosis000001
3Budget000002
2Budget000003
1Prognosis000002

 

Table 2:

Budgetdetails_IDMain AccountAmount
5605040000
5606035000
55050-20000
55040-45000
55030200000
3605045000
3606030000
35050-15000
35040-40000
35030200000
2605042000
2606035000
25050-20000
25040-40000
2503025000
4605030000
4606035000
45050-30000
45040-45000
4503010000
1605035000
1606020000
15050-20000
15040-20000
150305000



This is the wanted result, which is a table with the latest version of bugdet and prognosis

Main accountBudgetPrognosis
60504200035000
60603500020000
5050-20000-20000
5040-40000-20000
5030250005000


Thank you for your help. 

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, thyrast 

     

    Whether the format of Version can be converted to number for easy calculation.

    Measure:

    Budget = 
VAR _maxversion =
    CALCULATE (
        MAX ( 'Table 1'[Version] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[Type] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table 2'[Amount] ),
        FILTER ( ALL ( 'Table 1' ), [Type] = "Budget" && [Version] = _maxversion ),
        FILTER(ALL('Table 2'),[Main Account]=SELECTEDVALUE('Table 2'[Main Account])
    )
)
    Prognosis = 
CALCULATE (
    MAX ( 'Table 2'[Amount] ),
    FILTER (
        ALL ( 'Table 1' ),
        [Type] = "Prognosis"
            && [Version]
                = CALCULATE (
                    MAX ( 'Table 1'[Version] ),
                    ALLEXCEPT ( 'Table 1', 'Table 1'[Type] )
                )
    ),
    FILTER (
        ALL ( 'Table 2' ),
        [Main Account] = SELECTEDVALUE ( 'Table 2'[Main Account] )
    )
)

    Result:

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    thyrast Not clear to me how you arrived at your desired results. But, if you are trying to get the "latest" value somehow then you will need an Index or Date column in your 2nd table.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, thyrast 

     

    Whether the format of Version can be converted to number for easy calculation.

    Measure:

    Budget = 
VAR _maxversion =
    CALCULATE (
        MAX ( 'Table 1'[Version] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[Type] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table 2'[Amount] ),
        FILTER ( ALL ( 'Table 1' ), [Type] = "Budget" && [Version] = _maxversion ),
        FILTER(ALL('Table 2'),[Main Account]=SELECTEDVALUE('Table 2'[Main Account])
    )
)
    Prognosis = 
CALCULATE (
    MAX ( 'Table 2'[Amount] ),
    FILTER (
        ALL ( 'Table 1' ),
        [Type] = "Prognosis"
            && [Version]
                = CALCULATE (
                    MAX ( 'Table 1'[Version] ),
                    ALLEXCEPT ( 'Table 1', 'Table 1'[Type] )
                )
    ),
    FILTER (
        ALL ( 'Table 2' ),
        [Main Account] = SELECTEDVALUE ( 'Table 2'[Main Account] )
    )
)

    Result:

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.