Forum Discussion

aizamkamadin's avatar
aizamkamadin
Icon for Helper II rankHelper II
6 years ago

DAX 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...
aizamkamadin's avatar
aizamkamadin
Icon for Helper II rankHelper II
6 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    aizamkamadin
    Icon for Helper II rankHelper II
    6 years ago

    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