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.
In above table I need dax query for Training requirement(TR), if all users completed TR then TR status should be Completed. If any of the user is IN Progress or Not completed then status should be In Progress. similarly is all are Not started it should be Not started. This is like and operation
Can some one help on this ASAP
Solved! Go to Solution.
Hi @Anonymous
Here is the updated solution
TR Status =
VAR NormalValue = SELECTEDVALUE ( Tasks[Satus] )
VAR AllStatus = CALCULATETABLE ( VALUES ( Tasks[Satus] ), ALLEXCEPT ( Tasks, Tasks[Trainee] ) )
VAR Completed = FILTER ( AllStatus, Tasks[Satus] = "Completed" )
VAR NotStarted = FILTER ( AllStatus, Tasks[Satus] = "Not Started" )
RETURN
SWITCH (
TRUE ( ),
COUNTROWS ( AllStatus ) = COUNTROWS ( Completed ), "Completed",
COUNTROWS ( AllStatus ) = COUNTROWS ( NotStarted ), "Not Started",
"In Progress"
)
Hi @Anonymous
Here is a sample file with the solution https://we.tl/t-RWLCO2vdgj
TR Status =
VAR NormalValue = SELECTEDVALUE ( Tasks[Satus] )
VAR AllStatus = VALUES ( Tasks[Satus] )
VAR Completed = FILTER ( AllStatus, Tasks[Satus] = "Completed" )
VAR NotStarted = FILTER ( AllStatus, Tasks[Satus] = "Not Started" )
RETURN
SWITCH (
TRUE ( ),
ISINSCOPE ( Tasks[Task] ), NormalValue,
COUNTROWS ( AllStatus ) = COUNTROWS ( Completed ), "Completed",
COUNTROWS ( AllStatus ) = COUNTROWS ( NotStarted ), "Not Started",
"In Progress"
)
Thanks for the query.. Almost worked. But for all completed also it is showing IN Progress.
Hi @Anonymous
Here is the updated solution
TR Status =
VAR NormalValue = SELECTEDVALUE ( Tasks[Satus] )
VAR AllStatus = CALCULATETABLE ( VALUES ( Tasks[Satus] ), ALLEXCEPT ( Tasks, Tasks[Trainee] ) )
VAR Completed = FILTER ( AllStatus, Tasks[Satus] = "Completed" )
VAR NotStarted = FILTER ( AllStatus, Tasks[Satus] = "Not Started" )
RETURN
SWITCH (
TRUE ( ),
COUNTROWS ( AllStatus ) = COUNTROWS ( Completed ), "Completed",
COUNTROWS ( AllStatus ) = COUNTROWS ( NotStarted ), "Not Started",
"In Progress"
)
Thanks a lot. This query worked. U r the master.
@Anonymous you need a DAX query / DAX calculated column / DAX measure to plot it eventually in a visual? 3 different things 🙂
Also, can you please provide a sample data to work on (not a screenshot)
I Need DAX Column and I don't have access to send data. Only I can send screenshots
@Anonymous
Result_CC =
SWITCH(
TRUE(),
'Table'[Task1] = "Not Started" && 'Table'[Task2] = "Not Started" && 'Table'[Task3] = "Not Started", "Not Started",
'Table'[Task1] = "Completed" && 'Table'[Task2] = "Completed" && 'Table'[Task3] = "Completed", "Completed",
"In Progress"
)
Thanks for the reply. I need query based on user and status.
Like in a Training 1 if all the users completed then in should be completed. Plz try on this
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |