DAX using IN

[aizamkamadin]

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

[amitchandak]

@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 user

only show one project per each user... i want to show count all project for the user

[aizamkamadin]

here is my issue..

https://community.powerbi.com/t5/Desktop/filter-visual-based-on-result-from-slicer/m-p/1273724#M5584...

[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.

[aizamkamadin]

@Greg_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.

[aizamkamadin]

@Greg_Deckler , 

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

[AntrikshSharma]

@aizamkamadin Try this.

=
CALCULATE (
    COUNT ( project_User[USERID] ),
    project_User[ProjectID] = 22
)