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
Anonymous
Not applicable

Using a string as a filter in calculate, weird behaviour.

Hey guys & gals, 

 

I've been struggling with a measure recently, and I dont fully understand its behaviour. Maybe one of you can enlighten me.

 

So I have  a really simple data model, with just 2 tables. 1 table with values in weeks, and one table with the weeks and an order for the weeks.

 

data model

 

Periods look like this: "PP" or "LY PP", periodID's are strings of numbers, so "1" and "2".

 

So then I create 2 measures, both use a sting comparison. Take all weeks where the program week name is "PP", and the other takes the program week ID, which is also a string, but features only numbers, so in this case PP is "1".

 

Here are the measures, they don't get much simpler than this 🙂

 

Measure using order period name =
CALCULATE(
      SUM('Values'[Value]),'Order'[period]="PP")

Measure using order periodid =
CALCULATE(
SUM('Values'[Value]),'Order'[PeriodID]="1")

Now the second one gives me my expected results, namely: only give me a value when the program week is PP, for all other weeks, do not give me a value.

however, the first one does something strange, and gives me a value for LYPP as well. See here:

Resulting values

 

Does anyone know why the behaviour of these two measures is different? What's the difference between filtering on "PP" vs "1"?  (changing the relationship between the tables from a repation between the ID's and the names have no impact on the end result)

 

I'm so confused!

4 REPLIES 4
waltheed
Solution Supplier
Solution Supplier

It's because of the combined filter context...

 

In your table the rows are filtered by period name. In your measure you replace the filter context explicitly by setting 'Order'[period]="PP". That means that you replace exactly the filter that is used for the rows. Therefore the rows do not matter anymore, it just gives you the PP total for all rows.  

 

You do not have this issue when using any other column than period name. Then the filter on the first row is: period name = "PP" and period ID = 1, that gives you a result. On the second row it's period name = "PP LY" and period id = 1 which returns nothing. 

 

Makes sense?

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Anonymous
Not applicable

Hmm I think I get what you are saying, but shouldnt the filter coming from the order table not then propagate to the values table by the relationship?

 

 

Anonymous
Not applicable

Also: is the right solution here to use KEEPFILTERS?

Like so:

 

Measure using values periodid =
CALCULATE(
SUM('Values'[Value]),KEEPFILTERS('Values'[period]="PP"))

Yes, the KEEPFILTERS() function could do what you want as well. 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

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.