Forum Discussion

DavePowerBI's avatar
DavePowerBI
Frequent Visitor
3 years ago
Solved

Distinct count measure Matrix visual filter issues

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 )

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

    • DavePowerBI's avatar
      DavePowerBI
      Frequent Visitor
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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