Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This is working, but I was wondering if someone had a better solution: better modeling or better performance. Also, is there a name for this problem?
Here's the basic data. Filtering a table with many components for each issue. Each component has its own row.
Fact Table
Issue # | Component |
1 | Cat |
1 | Mouse |
2 | Dog |
2 | Elephant |
3 | Dog |
3 | Gerbil |
3 | Mouse |
Issues should only show if ALL of their components are selected. If 2/3 components are selected, don't show the issue at all.
Below, I have 2 sets of visuals. In the first, all components are selected and all rows are shown. In the second set, all is selected except Elephant, so nothing from issue 2 is displayed.
I use a star schema with unidirectional filtering. FactTable with Component, and Issue dimensions.
I have a calc column (Fact Rows) in Issue to count how many rows in Fact Table. This replaces some fancy DAX in the [Visual Filter] measure. The fancy DAX didn't perform well with the large dataset this is for.
Issue[Fact Rows] column =
[Visual Filter] measure =
Solved! Go to Solution.
How about replacing _allRows with this?
CALCULATE ( COUNTROWS ( FactTable ), REMOVEFILTERS ( FactTable[Component] ) )
Try
Visual Filter 2 = IF( ISINSCOPE(FactTable[Issue #]),
var slicerValues = VALUES(Component[Component])
var issueValues = CALCULATETABLE( VALUES(FactTable[Component]), ALLEXCEPT(FactTable,FactTable[Issue #]))
return IF( ISEMPTY( EXCEPT( issueValues, slicerValues) ), 1 )
)
Thanks for the reply. This is some fancy DAX. I tried this and it causes all results to disappear. Also, I avoid IF statements and other branching on visual level filters because of performance. I like SQLBI's trick of using INT() to convert a boolean into a 1 or a zero.
That's odd, it worked in the demo file when I tried it.
Good tip about the INT, the SQL BI guys are amazing. I don't usually spend much time on optimizing as I don't work with particularly large datasets.
How about replacing _allRows with this?
CALCULATE ( COUNTROWS ( FactTable ), REMOVEFILTERS ( FactTable[Component] ) )
Thanks Alexis. That works if I do REMOVEFILTER(Component[Component] and put Component from Component dim and not from FactTable. It doesn't work if the table viz has FactTable[Component] with REMOVEFILTER( FactTable[Component]. I'm thinking this might be due to the autoexists issue. Understanding DAX Auto-Exist - SQLBI, but I'm not going to think about that now.
Yes, removing the filter on the dimension table is the correct way to do it. I didn't initially notice that you had a separate table you were using for the slicer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |