Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cassidy
Power Participant
Power Participant

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

cassidy_0-1617144300492.png

 

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

 

1 ACCEPTED SOLUTION
cassidy
Power Participant
Power Participant

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.

View solution in original post

3 REPLIES 3
cassidy
Power Participant
Power Participant

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
Power Participant
Power Participant

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.