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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Impactful Individual
Impactful Individual

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

waltheed
Impactful Individual
Impactful Individual

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

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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