cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AW1976NOVA
Post Patron
Post Patron

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, @AW1976NOVA 

 

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, @AW1976NOVA 

 

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

VijayP
Super User I
Super User I

@AW1976NOVA 

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors