Report filter vs. DAX FILTER()

[danthu]

I'm new to PowerBI and a little bit confused about different types of filters and relationships. Basically what I want to ask is if the filters that I choose in the report to filter my visuals can be propagated into DAX expressions such as calculated columns and measures? Or is it only possible to filter these by hard-coding the filters into the DAX expressions?

Here is an example. I have a calulated column given by

count = 
CALCULATE(COUNTROWS(Kundlista); FILTER(Kundlista; Kundlista[leadTime] < Konvertering[CohortAge]))

I would like to be able to filter the data that this calculation acts on by adding a filter on the table 'Organizations'  which have a single direction relation to 'Kundlista'. When I do this in the same expression it works as expected. I filter 'Organizations' to only include the rows with 'Organizations[registeredDate]' after a certain date. This in turn propagates to only including the corresponding rows in 'Kundlista' because of the relationship.

count = 
CALCULATE(COUNTROWS(Kundlista); FILTER(ALL(Kundlista); Kundlista[leadTime] < Konvertering[CohortAge]); FILTER(Organizations; Organizations[registeredDate] > DATE(2010;1;1)))

However when I try to filter on the 'Organization[registeredDate]' column in a visual in the report nothing happens. I would be grateful if someone could explain to me exactly what happens here. Are the filters I apply to the visual not affecting the DAX expression? It seems like my relationships are correct since it works when applying the filter in the DAX expression, right?

If that is the case, is there anyway to let the visual filter affect the measures and calculated columns behind the scenes?

Thanks you!

[danthu]

Sorry if I described my question in a somewhat unclear manner. Basically I wonder how the visual filters that I apply in the report affects the calculated measures and columns created by DAX expressions in the data view. It seems like the visual filters does not affect the measures and calculated columns, at least not in the same way as filters entered directly into the DAX expressions?

This is probably a broad and not very well-formulated question but I hope you understand what I mean. I have read the article suggested by I cannot find any specific info about visual filters in there. I would be very grateful if someone could give me a quick explanation on this topic or point me in a direction as to where to start learning about this.

[v-jiascu-msft]

@danthu

Hi,

First, filter and slicer won’t be propagated  to “Calculated Column”, which is create by adding “New Column”. Just take a “Calculated Column” as a normal column when you create reports.

Second, if you want to make the filter and the slicer work on a formula, you need to create a measure with the formula.

Third, the direction of the relationship will influence the filter and slicer. They both work along the direction of the relationship.

Fourth, your second formula looks great if it’s a measure. The filter on the “Organization[registeredDate]” column won’t work if the date smaller than DATE(2010;1;1). Because this is already filtered by the formula. Please check it out.

Fifth, some functions will ignore or alter the outcome of filter and slicer. Such as, ALL, ALLEXCEPT, ALLSELECTED.

I hope this would help. 

Best Regards!

Dale

[danthu]

@v-jiascu-msft Thank you! Much appreciated!

That clarifies some things. So if I understand you correctly, report filters and slicers do propagate to measures but not to calculated columns? Calculated columns are treated as normal columns with only the values and not the code behind it with respect to report filters and slicers. Correct?

One more related thing that I am not sure about is how the legend option in a stacked chart works. I figured it should work as several filters applied one at a time, with each legend representing applying a filter to only include the data corresponding to the filter. Then adding these together to get the total. However this does not seem to be the case in what I'm working on at the moment. I will try to describe the situation.

I have a measure to calculate the cumulative count as:

kumulativaKonvert = CALCULATE(COUNTROWS(Kundlista); FILTER(ALLSELECTED(Kundlista); Kundlista[monthRegistered] <= MAX(Kundlista[monthRegistered])))

It works just fine and displays in a visual as expected when no additional filter is added. I can also add a filter to the visual that filters on Kundlista[monthRegistered] and it works as expected calculating the cumulative sum on the data corresponding only to the chosen monthRegistered. However when I add Kundlista[monthRegistered] as legend it does not function as I expect it to. It does not apply a filter of only one monthRegistered at a time to calculate each individual cumulative sum and then add them together in the total. Instead it gives very strange results, and the individual cumulative sums for each monthRegistered becomes almost as large as the total cumuluative sum should be.

So I would be grateful if someone could explain what adding a legend to the visual does in detail in this situation. And also suggestions on how to get the cumulative sum to display correctly with each legend corresponding to the cumulative sum of only one monthRegistered.

Thanks!

[v-jiascu-msft]

Hi @danthu,

Report filters and slicers change (or provide ) the context of the measure. We can call it "propagate" in some way. Yes, we only use the values of the calculated columns in the report. 

I would suggest you create a new post of the other question with some snapshots. It will be better for others to search this forum for help.

Best Regards!

Dale

[Sunkari]

By default, the visual filter will be propagated to all DAX expressions. Whenever multiple contexts are operating on a  single DAX expression(expression with calculate keyword), one context will be overridden by other. That's why sometimes you will see different results based on filter condition placement.

As already suggested go through the following links.

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

https://ptgmedia.pearsoncmg.com/images/9780735698352/samplepages/9780735698352.pdf

[sdjensen]

@danthu - did you read this blog from SQLBI? https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

[danthu]

@sdjensen - Yes I did actually. But I do not understand how the report filters in PowerBI relates to this? It is clearly not the same thing to specify a filter within a DAX statement and to specify is from the report visual?