Forum Discussion
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_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.
Hi Anonymous ,
If you want to use measure and matrix, you can create this measure:
alert_count = COUNT ( store_loc[emp_id] ) - 1Put 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.
6 Replies
Anonymous add following measure and then use it in visual left filter
Count = COUNTROWS ( Table ) - 1add 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.⚡
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 )Hi,
Share some data, describe the question and show the expected result.
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.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.
Hi Anonymous ,
If you want to use measure and matrix, you can create this measure:
alert_count = COUNT ( store_loc[emp_id] ) - 1Put 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.
