Count Table Distinct

[joshua3m]

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

[tamerj1]

Hi @joshua3m 
Please try

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

[joshua3m]

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 )

---

[tamerj1 · Accepted solution]

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