How can I make my visual distinct count total match the row by row sum when applying filters in PowerBI

Question

I have a report that includes sales codes and I need my visuals to show the distinct count of the sales codes. I used to filter by month and then realized than in the distinct count formula I need to force a row by row context, as whenever I applied the month filter the visual distinct count total didn´t match the row by row sum.

I applied this formula and when filtering by month, the row by row sum was equal to the distinct count total:

SumX Measure = 
SUMX(
    VALUES( 'Dates Table'[YearMonthnumber] ),
    CALCULATE(
        DISTINCTCOUNT( '2020'[Sales Code] )
    )
)

However, now I need to use other filters rather than only the month, but when I filter according to other variables, again I have the problem that the distinct count total is not matching the row by row sum. I need the visual to have the correct distinct count total (matching with the row by row sum) by selecting different filters.

I just want the distinct count to be correct according to the filters I select, there are four filters from the same table (product, country, company, customer ID) and one filter from a dates table. I attach an example so you can better understand what I need to do, my file includes the filters I usually apply and other dax formulas I have tried: Distinct Count Error

Best

JLV

Gitika · Answer 1 · Jul 28, 2020

The COUNTROWS function can be used to count the unique values available in a column for the current filter context:

SumX Measure =
SUMX(
    VALUES( 'Dates Table'[YearMonthnumber] ),
    CALCULATE(

                COUNTROWS ( DISTINCT ( table[column] ) 

                   DISTINCTCOUNT ( table[column] ) ) 

  )

According to your query, this will be helpful.