Forum Discussion

freemainia's avatar
freemainia
Icon for Helper I rankHelper I
3 years ago
Solved

COUNTIF measure

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 1Project 2Project 3What I want
IDTrain
1Yes
2No
3No
4Partial
5No

 

IDTrain
1 No
2 No
3 Partial
4 No
5Partial

 

IDTrain
1 No
2 No
3 No
4 Yes
5Partial

 

IDTrain
1 Yes
2 No
3 Partial
4 Yes
5Partial

 

 

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.

 

 

 

 

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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")