Forum Discussion

Snowy34's avatar
Snowy34
Icon for Helper III rankHelper 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
Icon for Community Champion rankCommunity Champion
2 years ago

If I was going to do this in Power Query:

Add a custom column (= 1).  Call it NewCol, this makes the next step easier.

Pivot the Status column (from the Transform menu) using NewCol as Values with an aggregation of Count.

This will give you more-or-less the picture above.

The new Status column is basically the same as the CompetentStatus column.

--

Comparing with the logic from the original post: it would be-

If a User has achieved Competent status then Competent, otherwise Not Competent

Let me know what you think