How to create measures that function across multiple columns

[AW1976NOVA]

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.

[v-alq-msft · Accepted solution]

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:

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.

[VijayP]

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