Forum Discussion
Filter a Measure by a Measure Issue
Hello,
I'm trying to Filter a Measure with another Measure, not sure why I'm getting a blank result
This is the goal Measure, it's Calculating an already established measure and filtering my Calendar month to be equal to a Measure that currently equals "12".
DemandPrPrMth = CALCULATE([Demand Sls $],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= 'Fiscal Calendar'[PriorPriorMthNumber]))The output is Blank, no result.
Here is the result of the "PriorPriorMthNumber" which is a Measure that brings back 2 months ago on our Fiscal Calendar:
PriorPriorMthNumber = if([PriorMthNumber]-1=0, CALCULATE(MAX('Fiscal Calendar'[FiscalPeriodMonth]),FILTER('TY/LY Map','TY/LY Map'[Year Type]="LY")),[PriorMthNumber]-1)
This is saying if Last Month - 1 = 0 (invalid), then bring back the Max Month from Last Year (which is 12 at the moment).
If I adapt the Measure to simply Filter to "12", I get the intended result.
DemandPrPrMth = CALCULATE([Demand Sls $],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= 12))If I adapt the Measure to Filter to a test Measure that is " = 12 ", that also gets the intended result.
12 = 12
So "12" is a valid value to filter to, and my original PriorPriorMthNumber Measure generates "12", but I'm getting a blank result when I use it as the Filter.
Any thoughts? Thanks
I was able to get this to work by defining my PriorPriorMthNumber Measure as a Variable.
So instead of
DemandPrPrMth = CALCULATE([Demand Sls $],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= 'Fiscal Calendar'[PriorPriorMthNumber]))I'm doing
Demand Sls $ PrPr Mth = VAR PrPrMth = [PriorPriorMthNumber] RETURN CALCULATE([Demand Sls $ TY],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= PrPrMth)))Slightly different metrics between the two, but the point is I defined the Measure I wanted to filter by as a Variable first and then filtered on the Variable. No idea why, I guess the Variable is formatted in a way the query understands.
3 Replies
I was able to get this to work by defining my PriorPriorMthNumber Measure as a Variable.
So instead of
DemandPrPrMth = CALCULATE([Demand Sls $],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= 'Fiscal Calendar'[PriorPriorMthNumber]))I'm doing
Demand Sls $ PrPr Mth = VAR PrPrMth = [PriorPriorMthNumber] RETURN CALCULATE([Demand Sls $ TY],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= PrPrMth)))Slightly different metrics between the two, but the point is I defined the Measure I wanted to filter by as a Variable first and then filtered on the Variable. No idea why, I guess the Variable is formatted in a way the query understands.
I've come up with a workaround by throwing in an If statement where I check to see if PriorPriorMthNumber is "12", and if so Filter the Calendar to "12". Still don't understand why I can't just Filter with PriorPriorMthNumber directly seeing as how it obviously equals "12".
DemandPrPrMth = if([PriorPriorMthNumber]=12,CALCULATE([Demand Sls $ LY],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= 12)),CALCULATE([Demand Sls $ TY],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalPeriodMonth]= [PriorPriorMthNumber])))Hi cassidy
What visual do you place the measure [DemandPrPrMth] in? Maybe there is some field in the visual that provides a context to influence the measure. You may add [PriorPriorMthNumber] Measure into the same visual to test whether its result is still 12 or blank when it's in the same context. Do not remove other fields if there is any.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as solution to help other members find it.
