Calculate with filter expressions

Anonymous — Mon, 30 Jan 2023 09:20:39 GMT

Hello,

I have following measure which doesn't work when i am using variable varRank2 as a condition with CurrentVal.

Ratio Kms For Rank2 To Actual = VAR varRank2 = 'FPOC Main'[Rank 2 Kms By Month] VAR TotalActual = CALCULATE( MAX('FPOC Main'[ActualSumFromViewSummary]), 'FPOC Main'[FPOC_Date] = SELECTEDVALUE(DimDate[DateInDateFormat]) ) VAR CurrentVal = CALCULATE( SUM('FPOC Main'[kms]), FILTER( 'FPOC Main', 'FPOC Main'[Measurement_End_Code] = varRank2 ) ) VAR Ratio = DIVIDE( CurrentVal, TotalActual, 0 ) RETURN CurrentVal

varRank2 returns text value which should filter 'FPOC Main' table to second Measure_End_Code name based on calculated percentage.
Measure Rank 2 Kms By Month is calculated as follows:

Rank 2 Kms By Month = CALCULATE( MAX('FPOC Main'[Measurement_End_Code]), FILTER( VALUES('FPOC Main'[Measurement_End_Code]), RANKX( ALL('FPOC Main'[Measurement_End_Code]), CALCULATE('FPOC Main'[% Kms By Month]))=2 ) )

and when i put that value in card visual it shows correct value. Further more, there are also Rank1 measures build in the same way and those seems to work, so filter expression in CurrentVal works.

One more thing, when i put literal value instead of a variable value it works.
Can't figure it out, no idea what is going on.

Any help?

Re: Calculate with filter expressions

amitchandak — Tue, 31 Jan 2023 02:25:27 GMT

Anonymous , I think you should try new Index function , that can help

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

topic Re: Calculate with filter expressions in DAX Commands and Tips

Calculate with filter expressions

Re: Calculate with filter expressions