CountIf row contains at least 1 value

[Anonymous]

I am trying to count the number of contacts with at least 1 opt'in = "Yes". 

I have been able to find the total number of opt'ins (calculate count each column = "Yes") but I cannot figure out how to count across rows if at least 1 of the values = "yes". 

If the Contact has a least 1 opt'in ="Yes" then the new column will have a 1. If the Contact has all opt'ins = "No" then the column will have a 0. 

Here is a screenshot excel example of what i am looking for. Getting Column H is the issue. 

Thank you!

[BA_Pete · Accepted solution]

Hi @Anonymous ,

In Power Query, you can add a Custom Column that will output a countable value based on your criteria as follows:

if List.Contains(
    {[#"Opt'in 1"],[#"Opt'in 2"],[#"Opt'in 3"],[#"Opt'in 4"],[#"Opt'in 5"],[#"Opt'in 6"]},
    "Yes"
)
then 1 else 0

Pete 

[Sujit_Thakur · Accepted solution]

Dear @Anonymous ,

here is your solution 
PBIX - https://drive.google.com/file/d/1c1NDQ1yBW4ws_JGVtbCaAW9_dyaTa5NF/view?usp=sharing

Steps are as follows 
1)after get data tranform it
after getting data transform it

2)select all opt columns 

select all colums with opt named

3)unpivot selected columns using Transform ribbon 

unpivot it using transform tab as shown

5) This is how data will look

this is how data will look after unpivoting

6) use measure as i used in PBIX file attached above as a google drive link

Please dont forget to give kudos on my post to keep me motivated .
And if this helped please accept as solution
if any doubt please  let me know 

Happy to help
regards 
thakur sujit

[Icey · Accepted solution]

Hi @Anonymous ,

If you choose to change the data structure, try the method @Sujit_Thakur provided. 

In addition, you can also modify the measure like below to create a Matrix visual the same as you posted.

Measure = 
IF (
    HASONEVALUE ( Data[Attribute] ),
    MAX ( Data[Value] ),
    IF ( COUNTROWS ( FILTER ( Data, Data[Value] = "Yes" ) ) >= 1, 1, 0 )
)

If you don't want to change the data structure, you can try to add a custom column in Power Query Editor as @BA_Pete did.

Or, you can create a calculated column or measure using DAX like so:

Contact has a least 1 opt'in column = 
IF (
    [opt 1] = "Yes"
        || [opt 2] = "Yes"
        || [opt 3] = "Yes"
        || [opt 4] = "Yes"
        || [opt 5] = "Yes",
    1,
    0
)

Contact has a least 1 opt'in Measure = 
IF (
    MAX('Table (2)'[opt 1]) = "Yes"
        || MAX('Table (2)'[opt 2]) = "Yes"
        || MAX('Table (2)'[opt 3]) = "Yes"
        || MAX('Table (2)'[opt 4] )= "Yes"
        || MAX('Table (2)'[opt 5]) = "Yes",
    1,
    0
)

BTW, .pbix file attached. (PS: I use the file @Sujit_Thakur provided.)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it more quickly.

[Sujit_Thakur · Accepted solution]

Dear @Anonymous ,

here is your solution 
PBIX - https://drive.google.com/file/d/1c1NDQ1yBW4ws_JGVtbCaAW9_dyaTa5NF/view?usp=sharing

Steps are as follows 
1)after get data tranform it
after getting data transform it

2)select all opt columns 

select all colums with opt named

3)unpivot selected columns using Transform ribbon 

unpivot it using transform tab as shown

5) This is how data will look

this is how data will look after unpivoting

6) use measure as i used in PBIX file attached above as a google drive link

Please dont forget to give kudos on my post to keep me motivated .
And if this helped please accept as solution
if any doubt please  let me know 

Happy to help
regards 
thakur sujit

[Anonymous]

Such data is not good for Power BI consumption (and hence your problems). Please unpivot the columns from B through to the end and you'll have a shape that is correct for modeling in PBI.

[amitchandak]

@Anonymous , First unpivot the data

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Post that have formula like

countx(filter(summarize(Table, Table[contactid], "_1", calculate(countrows(Table),Table[Optin]="Yes")),[_1]>=1),[contactid])

[BA_Pete · Accepted solution]

Hi @Anonymous ,

In Power Query, you can add a Custom Column that will output a countable value based on your criteria as follows:

if List.Contains(
    {[#"Opt'in 1"],[#"Opt'in 2"],[#"Opt'in 3"],[#"Opt'in 4"],[#"Opt'in 5"],[#"Opt'in 6"]},
    "Yes"
)
then 1 else 0

Pete 

[Anonymous]

Hi @BA_Pete thank you it worked like a charm!

I am able to get the column in the power query editor with no errors but when i click close and apply i get the error "OLE DB or ODBC error: [DataSource.Error] The request was aborted: The request was canceled.."

Do you know why?