Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Anonymous
Not applicable

How to create measures that function across multiple columns

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:

99.PNG

 

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:

100.PNG

 

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.

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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:

c1.png

 

You may go to Power Query=>click 'Transform'=>make 'C1'-'C6' selected=>click 'Unpivot Columns'.

b2.png

 

c3.png

 

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:

c4.png

 

Best Regards

Allan

 

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

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
Community Support

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:

c1.png

 

You may go to Power Query=>click 'Transform'=>make 'C1'-'C6' selected=>click 'Unpivot Columns'.

b2.png

 

c3.png

 

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:

c4.png

 

Best Regards

Allan

 

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

VijayP
Super User
Super User

@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 !




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.