Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count in SQL to DAX

I am able to get a count in SQL. Trying to do the same with DAX measure is not resulting in the correct output. How would you translate the following:  SELECT store_num, COUNT(emp_id)-1 AS alert_co...
Need Help
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    If you want to use measure and matrix, you can create this measure:

    alert_count =
COUNT ( store_loc[emp_id] ) - 1

    Put it in Matrix, and create another measure like this, set its value as 1 and put it in the visual filter:

    visual control =
IF ( [alert_count] > 1, 1, 0 )

    If you do not want to use this filter measure, you can filter it directly by [alert_count]:

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago

Hi, Anonymous , you might want to try creating a calculated table this way,

 

Summary = 
SUMMARIZECOLUMNS (
    store_loc[store_num],
    FILTER (
        DISTINCT ( store_loc[store_num] ),
        CALCULATE ( COUNTROWS ( store_loc ) ) > 1
    ),
    "alert_count", COUNTROWS ( store_loc ) - 1
)