Reply
Member
Posts: 63
Registered: ‎02-22-2016

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 Smiley Happy

 

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!

Advisor
Posts: 120
Registered: ‎11-10-2015

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

[ Edited ]

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
Power BI User Group Netherlands
Member
Posts: 63
Registered: ‎02-22-2016

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

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?

 

 

Member
Posts: 63
Registered: ‎02-22-2016

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

Also: is the right solution here to use KEEPFILTERS?

Like so:

 

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

Highlighted
Advisor
Posts: 120
Registered: ‎11-10-2015

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

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

Cheers, Edgar Walther
Power BI User Group Netherlands