Count in SQL to DAX

[Anonymous]

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_count
FROM store_loc
GROUP BY store_num
HAVING COUNT(emp_id) > 1;

Any advice on translating the above into a measure will be greatly appreciated. 

[v-yingjl]

Hi @Anonymous ,

Based on your description, I have created this simple sample in sql server and run the sql statement to get the initial expected output:

sample tablesql result

To get the same result in power bi, you can create this calculated table by DAX:

Table =
FILTER (
    SUMMARIZE (
        'store_loc',
        store_loc[store_num],
        "alert_count", COUNT ( store_loc[emp_id] ) - 1
    ),
    [alert_count] > 1
)

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.

[Anonymous]

@v-yingjl Thank you for your support. This is what I am looking for but is this possible to execute just as a measure and not as a calculated table? My ultimate goal is to create a matrix table that will be dynamic. Please let me know if I am to vague and I can try to elaborate with more detail. 

[v-yingjl · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[CNENFRNL]

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
)

[parry2k]

@Anonymous add following measure and then use it in visual left filter

Count = COUNTROWS ( Table ) - 1

add a table visual, put store number and this new measure and in visual left filter choose where count > 1

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡