Forum Discussion
Helper IReport filter vs. DAX FILTER()
Helper ISorry 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.
Microsoft Employee
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