I have a database for trained people that looks like this Training IDTrainee IDTrainee site IDAM1S1BM1S1DM2S1EM3S2AM4S3BM5S4AM6S4EM6S4BM7S5CM2S1BM4S3 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 :NbTrainingA= calculate(distinctcount(Training[Trainee ID]),Training[Training ID]="A")NbTrainingB= calculate(distinctcount(Training[Trainee ID]),Training[Training ID]="A") But I don't know how to go from counting how many people performed A (3 in the example above) and how many performed B (4 in the example above) to how many performed A and B (2 in the example above)

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])RETURNCOUNTROWS ( 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/

Count / different if on different lines | Microsoft Fabric Community

14 Replies

Jihwan_Kim
Super User
5 years ago
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/
Arkhos94
Helper IV
5 years ago
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 ?
NbTrainingAandB = calculate(distinctcount(Training[Trainee ID]),Formations[Training ID]="A" && Formations[Training ID]="B")
- Jihwan_Kim
  Super User
  5 years ago
  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/
Arkhos94
Helper IV
5 years ago
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
- Jihwan_Kim
  Super User
  5 years ago
  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?
Arkhos94
Helper IV
5 years ago
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 ?
- Jihwan_Kim
  Super User
  5 years ago
  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/
Arkhos94
Helper IV
5 years ago
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 :

Trainee AandB =
VAR newtable = SUMMARIZE(Training,Training[Trainee ID] ,
"Training A",calculate(DISTINCTCOUNT(Training[Trainee ID]),Training[Training ID]="A"),
"Training B",calculate(DISTINCTCOUNT(Training[Trainee ID]),Training[Training ID]="B"),
RETURN
XXXXX

If I understand how SUMMARIZE works, my new table is now a table with one line per trainee, with one column "Training A" (being 0 or 1 depending if the trainee performed A or not) and another column "Training B" (being 0 or 1 depending if the trainee performed B or not)

Am I correct ?

If yes, the answer to my problem would be to count the number of line with Training A>=1 and Training B>=1 (in the part after return where I wrote XXXX). Is it doable ?
- Jihwan_Kim
  Super User
  5 years ago
  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/
Arkhos94
Helper IV
5 years ago
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
Arkhos94
Helper IV
5 years ago
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)
Arkhos94
Helper IV
5 years ago
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 :
Site AandB =
VAR newtable2 =
SUMMARIZE (
FILTER (
Training,
CALCULATE (
DISTINCTCOUNT ( Training[Trainee site ID] ),
Training[Training ID] = "A"
) > 0
&& CALCULATE (
DISTINCTCOUNT ( Training[Trainee site ID] ),
Training[Training ID] = "B"
) > 0
),
Training[Trainee site ID]
)
RETURN
COUNTROWS ( newtable2 )

I thought it would work but the result is 1 for S1 and S3 but 0 for S4. Could you telle me what I did wrong ?
- Jihwan_Kim
  Super User
  5 years ago
  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.
  
  Site AandB =
  VAR newtable2 =
  SUMMARIZE (
  FILTER (
  Training,
  CALCULATE (
  DISTINCTCOUNT ( Training[Trainee site ID] ),
  FILTER( ALLEXCEPT(Training,Training[Trainee site ID]), Training[Training ID] = "A"
  )) > 0
  && CALCULATE (
  DISTINCTCOUNT ( Training[Trainee site ID] ),
  FILTER( ALLEXCEPT(Training,Training[Trainee site ID]), Training[Training ID] = "B"
  )) > 0
  ),
  Training[Trainee site ID]
  )
  RETURN
  COUNTROWS ( newtable2 )
  
  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/
Arkhos94
Helper IV
5 years ago
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

Forum Discussion

Count / different if on different lines

14 Replies

Related content

Different schema into different destination

How to Count Tuesday to friday Age count in different membe and Monday Age Count if different member

Page Level filter for different groups

Difference between different Staging lakehouses

How to get count from two different table

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