Forum Discussion
Helper IIDAX using IN
how to convert this sql query into DAX
SELECT COUNT(USERID) as TotalNo, User_ID FROM project_User
where UserID IN (Select UserID FROM project_User where ProjectID=22)
GROUP BY UserID
Thanks in advance
8 Replies
- AntrikshSharma
Community Champion
aizamkamadin Try this.
= CALCULATE ( COUNT ( project_User[USERID] ), project_User[ProjectID] = 22 ) - Greg_Deckler
aizamkamadin - COUNT would be COUNT or COUNTROWS, WHERE is FILTER, IN would potentially be IN, GROUP BY would be GROUPBY or SUMMARIZE.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
aizamkamadinGreg_Deckler , i try using your formula..but still failed to get what i want
StaffWorkload =CALCULATE(COUNT(project_user[USERID]),CALCULATETABLE(SUMMARIZE(project_user, project_user[USERID]),project_user[USERID] IN ALLSELECTED(project_user[GROUPID])))the graph show the staff involved in the selected project but the total of project only 1.how to show all workload for staff involved..- Greg_Deckler
aizamkamadin - Very difficult to troubleshoot without sample data and expected output from that data.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
- amitchandak
Super User
aizamkamadin , in any visual you can table user id on row/axis and use this measure
Total No= calculate(count(project_User[USERID]),project_User [ProjectID]=22)
You want to create a new table
summarize(project_User,project_User[USERID], "Total No",calculate(count(project_User[USERID]),project_User [ProjectID]=22))
- aizamkamadin
amitchandak, i tried amend the code..
StaffWorkload =CALCULATE(COUNT(project_user[USERID]),FILTER(project_user, ALLSELECTED(project_user[USERID])))but the data not showing total project for each useronly show one project per each user... i want to show count all project for the user
