Count / different if on different lines

[Arkhos94]

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 :

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)

[Jihwan_Kim · Accepted 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/

[Arkhos94]

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

[Arkhos94]

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]

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]

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]

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]

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 · Accepted solution]

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]

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 · Accepted 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/

[Arkhos94]

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]

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]

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]

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/

[Jihwan_Kim]

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/