Distinct count measure Matrix visual filter issues

[DavePowerBI]

Hi,

I have the following two measure but the issue I have is when I go to the filter visual for any of these two measures I get the advanced filter option and when I apply the filter Is greater than 1 or any other scenario I get the below, I have tried to change the format of the data and measure but makes no difference.

Not sure if something needs to be added to the measure calculation inorder for the visual  filter to work

Appreciate any help that can be provided. 

# Count Users in ID = DISTINCTCOUNT('DataTable'[Name])

# Highlight Rule = IF( HASONEFILTER('DataTable'[ID]) && 'DataTable'[# Count Users in ID] > 1, 1, 0 )

 

[Anonymous]

Hi @DavePowerBI,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[DavePowerBI]

@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

[Anonymous · Accepted solution]

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