Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Proud to be a Datanaut!
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
2)select all opt columns
3)unpivot selected columns using Transform ribbon
5) This is how data will look
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
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.
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.
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
2)select all opt columns
3)unpivot selected columns using Transform ribbon
5) This is how data will look
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 , 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])
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
Proud to be a Datanaut!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |