Forum Discussion

bk-ikram's avatar
bk-ikram
Frequent Visitor
2 years ago
Solved

DistinctCount , ignoring one filter

Hi,  Say I am working on the datamodel found in this report : https://tinyurl.com/2upv9t53 I want to create a similar table to this, where distinct count of customerIds are displayed for all row...
"DAX"
"FILTER"
distinctcount
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    bk-ikram Try the following. PBIX attached below signature.

     

    Measure = 
    VAR __Group = MAX( 'Customer'[CategoryGroup] )
    VAR __Sales = SUM( 'Sales'[Sales] )
    VAR __CountCustomers = 
        COUNTROWS( 
            DISTINCT(
                SELECTCOLUMNS(
                    FILTER( 
                        ALLSELECTED('Customer'),
                        [CategoryGroup] = __Group
                    ),
                    "__Customer", [Customer Name]
                )
            )
        )
    VAR __Result = IF( __Sales = BLANK(), BLANK(), __CountCustomers )
RETURN
    __Result

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

bk-ikram Try the following. PBIX attached below signature.

 

Measure = 
    VAR __Group = MAX( 'Customer'[CategoryGroup] )
    VAR __Sales = SUM( 'Sales'[Sales] )
    VAR __CountCustomers = 
        COUNTROWS( 
            DISTINCT(
                SELECTCOLUMNS(
                    FILTER( 
                        ALLSELECTED('Customer'),
                        [CategoryGroup] = __Group
                    ),
                    "__Customer", [Customer Name]
                )
            )
        )
    VAR __Result = IF( __Sales = BLANK(), BLANK(), __CountCustomers )
RETURN
    __Result

 

 

bk-ikram's avatar
bk-ikram
Frequent Visitor
2 years ago

Thank you Greg, this solution seems to do the trick. I just thought that there could be a more "straight-forward " solution. I would appreciate it if you could briefly explain why the formula I tried would not work.