Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a database for trained people that looks like this
Training ID | Trainee ID | Trainee site ID |
A | M1 | S1 |
B | M1 | S1 |
D | M2 | S1 |
E | M3 | S2 |
A | M4 | S3 |
B | M5 | S4 |
A | M6 | S4 |
E | M6 | S4 |
B | M7 | S5 |
C | M2 | S1 |
B | M4 | S3 |
The database has one line per training per trainee. Training ID have 5 possible value (A to E)
My goal is to be able to count :
- how many trainee performed both training A and B
- how many trainee performed both training C and D or C and E
Counting the number of trainee who performed one training is easy, for example :
Solved! Go to Solution.
Hi, @Arkhos94
Please try the below if it works.
Trainee AandB =
VAR newtable =
SUMMARIZE (
FILTER (
Training,
CALCULATE (
DISTINCTCOUNT ( Training[Trainee ID] ),
Training[Training ID] = "A"
) > 0
&& CALCULATE (
DISTINCTCOUNT ( Training[Trainee ID] ),
Training[Training ID] = "B"
) > 0
),
Training[Trainee ID]
)
RETURN
COUNTROWS ( newtable )
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Arkhos94
Thank you for your feedback.
I think your idea is also doable, but I have not yet tried.
The below picture and the link is what I have created by using your sample and my last measure.
Please kindly let me know which part is different, and why my visual is not showing blank.
I think I have missed something.
https://www.dropbox.com/s/ug1uswnwlh2as1u/arkhos.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
It works, thanks @Jihwan_Kim
I will have to work a bit more on summary/filter to understand why the "filter(allexcept" was necessary but thanks for the help
Hello @Jihwan_Kim
Sorry to disturb you again but could you help me just a bit more ?
I tried tweaking your formula so to count the site with at least one trainee who went to training A and one trainee who went to training B, which should be sites S1 (one trainee who got A and B) , S3 (one trainee who got A and B) and S4(one trainee who got A and another who got B)
I modified your formula this way :
Hi, @Arkhos94
Please try the below.
In this situation, trainee site ID = S4 is split into two trainee IDs, and the formula needs to remove this filter for each site IDs to combine all trainee id.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hello @Jihwan_Kim
I found where is the problem in my power bi, if not the why
I started from my example above and changed it step by step in my true database so to identify where the problem is
My real database doesn't have only the 3 column above, it has a dozen more column (useless here). If I delete all the useless column, it works fine.
If I keep some of them => it doesn't work. I will do a step by step adding and removing each column to understand why
Edit : I found the column that cause a problem : that's 2 column with a date (training start and end date) and 1 with a number (classroom ID)
I'm a bit clueless on why it doesn't work on my side
As you can imagine the user ID and training ID are a bit more complicated that what I put on the example, but nothing that should cause a problem (for example A is 704864 or M1 is M099344)
I did some tweeking in your formula and the problem come when I combine two training ID
If I try :
Trainee AandB =
VAR newtable =
SUMMARIZE (
FILTER (
Training,
CALCULATE (
DISTINCTCOUNT ( Training[Trainee ID] ),
Training[Training ID] = "A"
) > 0
&& CALCULATE (
DISTINCTCOUNT ( Training[Trainee ID] ),
Training[Training ID] = "A"
) > 0
),
Training[Trainee ID]
)
RETURN
COUNTROWS ( newtable )
The formula give a result. Same if both calculate includes Training[Training ID] = "B"or even Training[Training ID] = "C" .
But once I put two different training ID the result goes blank. No idea why but I will keep looking
I tried it but it gave me a blank result
But reading and trying to understand your formula gave me an idea. How about this :
Hi, @Arkhos94
Thank you for your feedback.
I think your idea is also doable, but I have not yet tried.
The below picture and the link is what I have created by using your sample and my last measure.
Please kindly let me know which part is different, and why my visual is not showing blank.
I think I have missed something.
https://www.dropbox.com/s/ug1uswnwlh2as1u/arkhos.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I'm not sure I understand your question
Let's put some context so I'm more clear
Let's say training A is "Risk when working with electricity"
And training B is "how to repair our brand electric cars"
I want to know how many trainee have done both training and so know what are the risk when working with electricity AND how to repair our brand electric cars (so they can repair the cars and not die doing it)
Does that make things more clear ?
Hi, @Arkhos94
Please try the below if it works.
Trainee AandB =
VAR newtable =
SUMMARIZE (
FILTER (
Training,
CALCULATE (
DISTINCTCOUNT ( Training[Trainee ID] ),
Training[Training ID] = "A"
) > 0
&& CALCULATE (
DISTINCTCOUNT ( Training[Trainee ID] ),
Training[Training ID] = "B"
) > 0
),
Training[Trainee ID]
)
RETURN
COUNTROWS ( newtable )
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Sorry, it's Training[TrainingID]. My power BI is in French so I translate the names in english before putting them here (formations in french = training in english)
NbTrainingAandB = calculate(distinctcount(Training[Trainee ID]),Training[Training ID]="A" && Training[Training ID]="B")
If we take a trainee with the same data as M1 in the table above, I get :
NbTrainingA = 1
NbTrainingB = 1
NbTrainingAandB = blank
Hi, @Arkhos94
Thank you for your feedback.
Now, I am confused.
Do you want to count how many trainees who took A and B?
or,
Doe you want to count M1 took how many A+B training?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hello @Jihwan_Kim and thanks for your help
It doesn't seem to work. The total of user who performed both A and B in the real database is 0.
To be sure, I used excel to find a user who performed both A and B, filtered my page on him and put NbTrainingA, NbTrainingB and NbTrainingAandB in a table
NbTrainingA is 1
NbTrainingB is 1
NbTrainingAandB is blank
Did I miswrote something ?
Hi,
Thank you for your feedback.
May I ask what is the Formations[TrainingID] ??
I thought it was Training[TrainingID]
Please check my measure again.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Arkhos94
Please try the below for A and B
NbTrainingA andB=
CALCULATE (
DISTINCTCOUNT ( Training[Trainee ID] ),
Training[Training ID] = "A"
&& Training[Training ID] = "B"
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |