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 Jul 25, 2020 in Power BI 3,932 views

## 1 answer to this question.

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] ) )

)``` answered Jul 28, 2020 by
• 65,970 points

## Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

## How do I format the KPI in Power BI

format the KPI by selecting the paint ...READ MORE

## How do I label bar graph with different colors based on values from different slicers?

If the user selects only one value ...READ MORE

## Using DAX calculation how to calculate monthly budget till today in power bi Desktop?

You can make use of this: MTD Budget ...READ MORE

## Error : Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup().

The error says you need to use ...READ MORE

## How to get month name from month number in Power BI?

You can use: MonthName = FORMAT(DATE(1, [Num], 1), ...READ MORE