COUNTIF measure

[freemainia]

Hi,

I would like to:

1. Count the number of IDs that have a "Yes" in any of the corresponding Train cells (answer = 2)

2. Count the number of IDs that don't have a "Yes", but do have a "Partial", in any of the corresponding Train cells (answer = 2)

3. Count the number of IDs that have a "No" in all of the corresponding Train cells (answer = 1)

Project 1	Project 2	Project 3	What I want
ID Train 1 Yes 2 No 3 No 4 Partial 5 No	ID Train 1  No 2  No 3  Partial 4  No 5 Partial	ID Train 1  No 2  No 3  No 4  Yes 5 Partial	ID Train 1  Yes 2  No 3  Partial 4  Yes 5 Partial

I would like this to be a measure (rather than a column) as sometimes I may filter to one project, two projects, or all three projects.

Cheers.

[Ashish_Mathur]

Hi,

Do you have each Project's data in a seperat table?

[freemainia]

Currently, they are in separate tables.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[freemainia]

Many thanks @Ashish_Mathur.

Seems to be working now, with the slight adjustment of changing:

Measure 3 = COUNTROWS(FILTER(VALUES(Data[ID]),[No count]=3))

to

Measure 3 = COUNTROWS(FILTER(VALUES(Data[ID]),[No count trains]=DISTINCTCOUNT(Data[Project])))

Having the value of "3" didn't work when less than 3 projects were selected. I think (hope) the addition of DISTINCTCOUNT(Data[Project]) controls for the number of projects selected.

[Ashish_Mathur]

You are welcome.

[amitchandak]

@freemainia , For the last columns in the power query a new column

Make sure the two header are merged in date

let 

_a = {[Project 1 Train], [Project 2 Train],[Project 3 Train]}

_b = if List.Contains(_a, "Yes") then "Yes" else if List.Contains(_a, "Partial") "Partial" else "No"

in

_b

Or New column in dax

var _a = {[Project 1 Train], [Project 2 Train],[Project 3 Train]}

_b = Switch(true(),
Containsstring(_a, "Yes") , "Yes",
Containsstring(_a, "Partial") , "Partial" ,
"No")