Forum Discussion

Snowy34's avatar
Snowy34
Helper III
2 years ago
Solved

Rebuild data table

Good afternoon Team,   I'm looking for help rebuilding this user info data table, long story short, I have a table that holds users' training status, I need a visualisation to tell me who has done ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Snowy34 ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a tbale.

    Table 2 = UNION(VALUES('Table'[status]),{"Status"})

    (3) We can create measures.

    Measure = COUNTROWS(FILTER('Table','Table'[status]=MAX('Table 2'[status])))
    Measure 2 = 
SWITCH(TRUE(),
CALCULATE(COUNT('Table'[status]),FILTER(ALLSELECTED('Table'),'Table'[User]=MAX('Table'[User]) && 'Table'[status]="Item revoked"))>=1,"Item revoked",
CALCULATE(COUNT('Table'[status]),FILTER(ALLSELECTED('Table'),'Table'[User]=MAX('Table'[User]) &&'Table'[status]="competent"))>=1,"competent",
"not yet competent")
    Measure 3 = 
var _table=SUMMARIZE(ALL('Table'),[User],"sta",[Measure 2])
var _a=MAXX(FILTER(_table,[User] in VALUES('Table'[User])),[sta])
return 
IF(MAX('Table 2'[status])="Status",_a,[Measure])

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

HotChilli's avatar
HotChilli
Community Champion
2 years ago

Can you clarify that logic please because it looks like every user would be 'not yet competent' ?

Please show the desired result as well. A column in the same table or a measure in a table visual?

Snowy34's avatar
Snowy34
Helper III
2 years ago

hi, HotChilli thank you for taking the time to help me out,

 

The ideal result would look something like this in the second table, as you can see in the first table this is how my data looks like and what I would like to have is a visual of the second table, the idea here is I just need to know who is not competent, note that I have listed all the possible scenarios.