topic Re: Need DAX quey for User completed all Trainings and if any of the user not completed show as in progr in DAX Commands and Tips

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

Anonymous — Wed, 27 Jul 2022 11:33:13 GMT

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

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

SpartaBI — Wed, 27 Jul 2022 11:39:52 GMT

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)

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

Anonymous — Wed, 27 Jul 2022 11:46:27 GMT

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

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

SpartaBI — Wed, 27 Jul 2022 11:58:59 GMT

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

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

Anonymous — Wed, 27 Jul 2022 12:11:09 GMT

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

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

tamerj1 — Wed, 27 Jul 2022 13:36:12 GMT

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

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

Anonymous — Wed, 27 Jul 2022 15:45:22 GMT

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

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

tamerj1 — Wed, 27 Jul 2022 15:57:39 GMT

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

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

Anonymous — Wed, 27 Jul 2022 16:34:24 GMT

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