Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have a problem that seems simple but is somewhat complicated to me. I'm working with a cumbersome excel table that another team in my workplace uploads to our organization's SharePoint 365 site. I've mapped to the SharePoint 365 Online List and now have the table in my Power BI Desktop report as the data source.
The table has many columns. Here is a dummy portion of the table:
As you can see, columns such as Initial Barriers, Initial BH Status, Initial Call Qstnr, Initial Care Coordination, Initial Care Plan Time Frame and Initial Case Acuity can only have one of two results..."Criteria Met" or "Criteria Not Met".
I want to end up having a table visual in Power BI that would look something like this:
These 6 columns are only 6 of over 50 I'm dealing with. The issue is that the data source is constantly changing and being updated. I considered creating individual measures for each column but that seems impractical and...like I said before the data is always changing. Columns are being re-named, being added/deleted.
Like the envisioned table visual I have above...I'd like to have some kind of measure ( or possibly two measures ) that I could create...that would count the instances of "Criteria Met" and "Criteria Not Met" for each column...and not have to worry about adding or updating.
Is this possible? I'm not sure where to start.
Solved! Go to Solution.
Hi, @Anonymous
I am sorry for the late reply. Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may go to Power Query=>click 'Transform'=>make 'C1'-'C6' selected=>click 'Unpivot Columns'.
Then you may create two measures as below.
Criteria Met =
COALESCE(
DIVIDE(
COUNTROWS(
FILTER(
'Table',
[Value]="CM"
)
),
COUNTROWS(
'Table'
)
),
0
)
Criteria Not Met =
COALESCE(
DIVIDE(
COUNTROWS(
FILTER(
'Table',
[Value]="CNM"
)
),
COUNTROWS(
'Table'
)
),0
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
I am sorry for the late reply. Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may go to Power Query=>click 'Transform'=>make 'C1'-'C6' selected=>click 'Unpivot Columns'.
Then you may create two measures as below.
Criteria Met =
COALESCE(
DIVIDE(
COUNTROWS(
FILTER(
'Table',
[Value]="CM"
)
),
COUNTROWS(
'Table'
)
),
0
)
Criteria Not Met =
COALESCE(
DIVIDE(
COUNTROWS(
FILTER(
'Table',
[Value]="CNM"
)
),
COUNTROWS(
'Table'
)
),0
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
It is possible by Converting all Columns ( Where the Result Criteria met or not met) by usin unpivot Columns
But the challenge is the change / Delete / Add data source . If the Columns are changin ofent the Unpivot column option will not work ! Which columns are majorly changing !
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
61 | |
46 | |
45 |