Rebuild data table

[Snowy34]

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 the training and who has not.

As you can see below, that is how the data would look like, user 1 has missed his training but eventually, he has completed it.

Basically, looking at the below statement.
if no show & not yet competent & competent would = competent

everything else would = not yet competent

UserID	Status
1	no show
1	competent
2	not yet competent
3	Item revoked
4	no show
4	no show
4	competent

[v-tangjie-msft · Accepted solution]

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]

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

[HotChilli]

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]

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.