DAX filter with multiple criteria

[thyrast]

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:

ID	Type	Version
5	Budget	000001
4	Prognosis	000001
3	Budget	000002
2	Budget	000003
1	Prognosis	000002

Table 2:

Budgetdetails_ID	Main Account	Amount
5	6050	40000
5	6060	35000
5	5050	-20000
5	5040	-45000
5	5030	200000
3	6050	45000
3	6060	30000
3	5050	-15000
3	5040	-40000
3	5030	200000
2	6050	42000
2	6060	35000
2	5050	-20000
2	5040	-40000
2	5030	25000
4	6050	30000
4	6060	35000
4	5050	-30000
4	5040	-45000
4	5030	10000
1	6050	35000
1	6060	20000
1	5050	-20000
1	5040	-20000
1	5030	5000

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

Main account	Budget	Prognosis
6050	42000	35000
6060	35000	20000
5050	-20000	-20000
5040	-40000	-20000
5030	25000	5000

Thank you for your help. 

[v-zhangti · Accepted solution]

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.

[Greg_Deckler]

@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.