Need DAX quey for User completed all Trainings and if any of the user not completed show as in progr

[Anonymous]

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

[tamerj1]

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"
    )

[Anonymous]

Thanks for the query.. Almost worked. But for all completed also it is showing IN Progress.

[tamerj1 · Accepted 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"
    )

[Anonymous]

Thanks a lot. This query worked. U r the master.

[SpartaBI]

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

[Anonymous]

I Need DAX Column and I don't have access to send data. Only I can send screenshots

[SpartaBI]

@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"
	)

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

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