Forum Discussion

joshua3m's avatar
joshua3m
Frequent Visitor
3 years ago
Solved

Count Table Distinct

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. ...
  • tamerj1's avatar
    tamerj1
    3 years ago

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

     

     

joshua3m's avatar
joshua3m
Frequent Visitor
3 years ago

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's avatar
tamerj1
Community Champion
3 years ago

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