Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
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
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?
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.
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |