Forum Discussion

cassidy's avatar
cassidy
Power Participant
5 years ago
Solved

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 ...
Need Help
  • cassidy's avatar
    cassidy
    5 years ago

    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.

cassidy's avatar
cassidy
Power Participant
5 years ago

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])))

 

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

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.