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

     

     

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