topic Re: Count Table Distinct in DAX Commands and Tips

Count Table Distinct

joshua3m — Tue, 22 Aug 2023 07:59:10 GMT

Hello,

I'm trying my journey with dax and i step into issue which i can't find a solution...
I have table with CityLocation and Value and want count number of CityLocation where value is > 0.

For below result it should be 2.

I can get eaisly count of CityLocation where Value is >0 however how i can count unique City ?

CityLocation	Value
Torotno1	0
Toronto1	3
NewYork2	0
Toronto3	-1
Denver1	0
Denver2	1

Re: Count Table Distinct

tamerj1 — Tue, 22 Aug 2023 08:34:18 GMT

Hi joshua3m
Please try

Count = CALCULATE ( DISTINCTCOUNT ( 'Table'[CityLocation] ), 'Table'[Value] > 0 )

Re: Count Table Distinct

joshua3m — Tue, 22 Aug 2023 08:42:11 GMT

Hi Tamerj ,

Would this not calculate distinct only for CityLocation where i want actually look into City and not City Location combine ?

I was trying to do as below:

COUNT: COUNTROWS( FILTER( ADDCOLUMNS( VALUES(LEFT([CITYLOCATION],LEN[CITYLOCATION]-1), "STOCK" , ([VALUE]) ), [STOCK] >0 ) )

tamerj1 wrote:
Hi joshua3m
Please try

Count = CALCULATE ( DISTINCTCOUNT ( 'Table'[CityLocation] ), 'Table'[Value] > 0 )

Re: Count Table Distinct

tamerj1 — Tue, 22 Aug 2023 09:00:35 GMT

joshua3m
I can assume from your code that Value is a measure. In this case please try

COUNT = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( VALUES ( 'Table'[CITYLOCATION] ), [VALUE] > 0 ), "City", LEFT ( 'Table'[CITYLOCATION], LEN ( 'Table'[CITYLOCATION] ) - 1 ) ) ) )