DistinctCount , ignoring one filter

Question

Hi,&nbsp;Say I am working on the datamodel found in this report :https://tinyurl.com/2upv9t53I want to create a similar table to this, where distinct count of customerIds are displayed for all rows (in this case it would be&nbsp; the number of customerIds in the category group, and ignore all filters on the customers table. So the expected outcome would be something like this:&nbsp;&nbsp;I have tried the formula in the screenshot below, but it seems to give me a cartesian product of some sort.&nbsp;&nbsp;&nbsp;Here is the Customer table showing its mapping with the customergroup table:&nbsp;

greg_deckler · Accepted Answer

bk-ikram&nbsp;Try the following. PBIX attached below signature.
&nbsp;
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
&nbsp;
&nbsp;

Forum Discussion

DistinctCount , ignoring one filter

2 Replies

Related content

DISTINCTCOUNT to ignore filters

DISTINCTCOUNT with filters

Make measure ignore specific filter

Ignore filter for a measure

Make DISTINCTCOUNT Measure ignore slicer