Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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))
@amitchandak, i tried amend the code..
@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.
@Greg_Deckler , i try using your formula..but still failed to get what i want
@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.
here is the details,
i got 3 table
1. Project (ID, ProjectName)
2. Project_user (ID, UserID, Group_ID)
3. Project_Userdetails (ID, StaffName, Last_Name, Work_Position, Work_department, Work_phone)
current issue
> my column chart doesnt show the output i want
i want slicer will filter the table and result of the table will reflect to the graph.
& expected output
> meaning, if i choose project ICxxx, system will filtered and show those who work on the project in table list
>> then the list of staff will be shown in graph., but the value is not 1 (as per current graph below )... mayb staff ADI have more than 5 project that he work on or TEN got 4 project in hand
>>> i want to see staff workload for selected project.
>>> this is a SQL query that i used to filter the staff workload
SELECT COUNT(USERID) as TotalNo, UserID FROM project_User
where UserID IN (Select UserID FROM project_User where ProjectID=[use slicer here])
GROUP BY UserID
@aizamkamadin Try this.
=
CALCULATE (
COUNT ( project_User[USERID] ),
project_User[ProjectID] = 22
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |