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...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

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