topic Re: Use measure in filter in DAX Commands and Tips

Use measure in filter

Arne — Sun, 15 Mar 2020 10:24:42 GMT

Hi need to include a measure into a filter to get dynamic calculations.

I have started to select the period with a slicer which gives my the period in a measure as below

Var Sort order period = SELECTEDVALUE(ProdUnitAnalyzeHyperionDimPeriodView[SortOrderPeriod])

In this example i choose "202002 Actual" in Var Sort order Period.

Then i would like to get the period value from the measure "Var Sort order period"

Into the calculation below

CALCULATE(SUM(ProdUnitAnalyzeScorecardView[Amount]);(ProdUnitAnalyzeScorecardView[Account]="SCEBIT");(ProdUnitAnalyzeHyperionDimPeriodView[SortOrderPeriod]=[Var Sort order period]))

CALCULATE(SUM(ProdUnitAnalyzeScorecardView[Amount]);(ProdUnitAnalyzeScorecardView[Account]="SC50");(ProdUnitAnalyzeHyperionDimPeriodView[SortOrderPeriod]= [Var Sort order period]))

which is not working.

When i typer in the period "202002 actual" in the measure below it works. but i need to change the period from a slicer to get the diagram to react quickly.

CALCULATE(SUM(ProdUnitAnalyzeScorecardView[Amount]);(ProdUnitAnalyzeScorecardView[Account]="SCEBIT");(ProdUnitAnalyzeHyperionDimPeriodView[SortOrderPeriod]="202002 Actual"))

CALCULATE(SUM(ProdUnitAnalyzeScorecardView[Amount]);(ProdUnitAnalyzeScorecardView[Account]="SC50");(ProdUnitAnalyzeHyperionDimPeriodView[SortOrderPeriod]="202002 Actual"))

Is there any solution on how to do this?

Arne

Re: Use measure in filter

Anonymous — Sun, 15 Mar 2020 13:49:30 GMT

[Total Amount] = SUM ( ProdUnitAnalyzeScorecardView[Amount] ) [Your Measure] = var __sortOrderPeriod = [Var Sort order period] var __numerator = CALCULATE ( [Total Amount], ProdUnitAnalyzeScorecardView[Account] = "SCEBIT", ProdUnitAnalyzeHyperionDimPeriodView[SortOrderPeriod] = __sortOrderPeriod ) var __denominator = CALCULATE ( [Total Amount], ProdUnitAnalyzeScorecardView[Account] = "SC50", ProdUnitAnalyzeHyperionDimPeriodView[SortOrderPeriod] = __sortOrderPeriod ) var __result = DIVIDE( __numerator, __denominator ) RETURN __result

I don't know anything about your model but I'd warn you against slicing directly on your fact table by the users. This is dangerous and may produce incorrect results you'll not even be aware of (since the great majority of people do not check their figures well against the source data). If you want to know why you should not do it, then you can search for an article on www.sqlbi.com; just type in the search box: auto-exist.

Best

Re: Use measure in filter

Greg_Deckler — Sun, 15 Mar 2020 13:54:55 GMT

Not following this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490