Forum Discussion
Solved
Filtering on a Matrix
Hi, This may be a simple solutuion, I dont use Power BI much. I'll include a sample of the issue. In a matrix, is there any way to only show rows if it has certain criteria? Here is the underlyin...
Ah, I see... But always up for a challenge. Try this as a filter:
FILTER 2 = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Payment Type] ), ALLEXCEPT ( 'Table', 'Table'[Claim #] ) ) = 2, 1, IF ( MAX ( 'Table'[Payment Type] ) = "ALT", 1 ) )
Here is one way. Create an unrelated table using:
Reference table = SUMMARIZE('Table','Table'[Claim #],'Table'[Payment Type])
Create a measure to use as a filter in the visual:
Filter =
VAR _ALT =
CALCULATETABLE (
VALUES ( 'Reference table'[Claim #] ),
FILTER ( 'Reference table', 'Reference table'[Payment Type] = "ALT" )
)
VAR _ALL =
VALUES ( 'Table'[Claim #] )
RETURN
COUNTROWS ( INTERSECT ( _ALT, _ALL ) )
Cretae the visual and add the [Filter] measure to the filter pane for the visual and set the value to 1
Thank you for your help PaulDBrown . Unfortunately, I cant create a new table because its connected to a live database. I appreciate your time!
Ah, I see... But always up for a challenge. Try this as a filter:
FILTER 2 = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Payment Type] ), ALLEXCEPT ( 'Table', 'Table'[Claim #] ) ) = 2, 1, IF ( MAX ( 'Table'[Payment Type] ) = "ALT", 1 ) )PaulDBrown thank you so much! This works! I appreciate your time!
