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

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'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