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.  

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 ? 

 

CityLocationValue
Torotno10
Toronto13
NewYork20
Toronto3-1
Denver10
Denver21
  • 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 )
        )
    )
)

     

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi joshua3m 
    Please try

    Count =
CALCULATE ( DISTINCTCOUNT ( 'Table'[CityLocation] ), 'Table'[Value] > 0 )
    • 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 )
        )
    )
)