Group and Count with DAX

[darko861]

Hi,

I have the following query in SQL server that I would like to recreate in Power bi with DAX. How can I create a measure or table with his query. Thank you very much.

[v_rpt_Service].[status_description] AS 'Label', COUNT([v_rpt_Service].[SR_Service_RecID]) AS 'Value'
FROM [v_rpt_Service]
WHERE ([v_rpt_Service].[Closed_Flag] = 0)
GROUP BY [v_rpt_Service].[status_description]
ORDER BY COUNT([v_rpt_Service].[SR_Service_RecID]) DESC;

Label	Value
Analyzing	409
Waiting On Customer: User Acceptance	95
Customer has updated	89
Waiting On Customer: More Info	72
On Hold	67
Analyzing (Third line)	65
Creating solution	49
Waiting On Customer: Approve Estimate	49
Internal	45
Delivered	27
Recieved	22
Waiting for build	19
Assigned	18
*New	16
Not Assigned	15
Estimating	14
Waiting On Development	10
Testing solution	9
Ongoing	9
Re-Opened	3
*Assigned	1

[amitchandak · Accepted solution]

@darko861 , Create a measure like this

calculate(COUNT(v_rpt_Service[SR_Service_RecID]) , filter(v_rpt_Service, v_rpt_Service[Closed_Flag] = 0))

Use that with status_description on the visual. Order bu you can sort on visual.

Check DAX vs SQL - https://www.youtube.com/playlist?list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj

[darko861]

thanks, but I got the following error message:

[darko861]

Thanks, I managed the conversion myself

[amitchandak]

@darko861 , can you share your formula?

If data type is an issue. check

calculate(COUNT(v_rpt_Service[SR_Service_RecID]) , filter(v_rpt_Service, v_rpt_Service[Closed_Flag] = "0"))

[darko861]

Hi Amit, I used the same formula. There was a data type issue with the source where 0 was converted to False and 1 was converted to True, so I made a quick conversion.

Thanks for the quick reply, it's much appreciated!