Calculate distinct count of the column based on the other values in the column

[abhishekpati86]

Above is the data I am working with on a Matrix Power BI Visualization. I want to add the distict count of the End User Trade Name as another column . So my New column will have the values 1,1,1,5 for the 4 distinct Corporate Account CSN and Corporate Account Name. 

When I am using Values part of the Matrix visualization then I am getting subtotals all around and it is becoming messy. Can you please help me with the DAX that calculates the distinct count of the End User Trade Name group by Corporate Account CSN and Corporate Account Name. 

[v-yulgu-msft · Accepted solution]

Hi abhishekpati86,

To count the row numbers based on group Corporate Account CSN and Corporate Account Name, please use the following DAX query to create a measure:

CountRow = CALCULATE(DISTINCTCOUNT(Sheet3[End User Trade Name]), ALLEXCEPT(Sheet3,Sheet3[Corporate Account Name],Sheet3[Corporate Account CSN]))

If you have any question, please feel free to ask.

Best regards,
Yuliana Gu

[ankitpatira]

@abhishekpati86 Why don't you use End User Trade Name column for the Values section of matrix -> click on dropdown icon and select Count(Distinct).

[abhishekpati86]

@ankitpatira- I have tried that option but it doesn't work as I have lot of amount columns to the right of the data and it creates lot of subtotal rows on the fly. I think the best option will be to duplicate the table, do a group by to get the count and get the count by merging those 2 tables.