Forum Discussion
Distinct count measure Matrix visual filter issues
Hi DavePowerBI,
You can use the following measure formula on the visual level filter to filter records:
flag = VAR summary = SUMMARIZE ( ALLSELECTED ( 'Table' ), [Country], [ID], "c_Name", DISTINCTCOUNT ( 'Table'[Name] ) ) RETURN IF (COUNTROWS ( FILTER ( summary, [c_Name] > 1 && [ID] IN VALUES ( 'Table'[ID] ) ) ) > 0, 1, 0)Regards,
Xiaoxin Sheng
Anonymous All,
As requested, please find the data below along with all the rows and columns for the matrix. I need to be able to find out which IDs has >1 name under it and illustrate this including the Names and Countries.
I cannot filter by #Count Users in ID or #highlight Rule columns.
I have also tried to see if I was able to do a work around with a visual conditional background colour formatting on the #Count users in ID column which works (see screenshot) but the visual filter does not allow me to filter by highlighted cell or number. Thank you for assistance.
Expected output or similar
RAW DATA
Country | ID | Name |
USA | 121 | Tony Smith |
USA | 121 | Teri Harry |
UK | 212 | Rob T |
UK | 212 | Sam L |
UK | 212 | Nick T |
UK | 214 | Fred P |
FRANCE | 811 | Tina B |
FRANCE | 812 | Brad H |
FRANCE | 813 | Lipi G |
FRANCE | 814 | Honey G |
FRANCE | 815 | Wes D |
USA | 122 | Jame Spot |
USA | 123 | Chris Martin |
USA | 124 | Adam N |
USA | 125 | Paul S |
USA | 126 | Matt R |
Hi DavePowerBI,
You can use the following measure formula on the visual level filter to filter records:
flag =
VAR summary =
SUMMARIZE (
ALLSELECTED ( 'Table' ),
[Country],
[ID],
"c_Name", DISTINCTCOUNT ( 'Table'[Name] )
)
RETURN
IF (COUNTROWS ( FILTER ( summary, [c_Name] > 1 && [ID] IN VALUES ( 'Table'[ID] ) ) ) > 0, 1, 0)Regards,
Xiaoxin Sheng