cancel
Showing results for
Did you mean:
Helper II

## Best model to achieve objective

I have a requirement which sounds simple but is causing me to scratch my head!

The setup I have is a company table, with company names and 5 value fields:

 Company Name Value 1 Value 2 Value 3 Value 4 Value 5 Acme 0 0 1 3 2 Buffalo 1 1 5 1 2 Cincinatti 2 0 0 1 7 Delta 2 0 4 2 1 Enron 4 22 2 0 1 Figaro 2 2 0 0 0

We need to ascertain for each value, for each company if the number in the value column is greater than zero (then "Yes"), or if it's zero (then "No").

The ask is to create a view as follows:

 Value 1 Value 2 Value 3 Value 4 Value 5 Yes 5 3 4 4 5 No 1 3 2 2 1

The data is coming from a SQL server. If the data was organised as follows, this would be easy to do.

 Company Metric Value Acme Value 1 No Acme Value 2 No Acme Value 3 Yes Acme Value 4 Yes Acme Value 5 Yes Buffalo Value 1 Yes Buffalo Value 2 Yes Etc Etc Etc

Any suggestions as to how I might bo about solving this problem?

Things I have considered:

• Creating a calculated column for each value in the company table and giving this a Yes or No value. However, this doesn't allow me to create the view I wish to get because I want to see all the Yes/No counts for every pillar side by side so what would I put in the rows of a matrix?
• I am working on a view of the data that pivots it to show the data as per the above but struggling a bit with this.

If anyone has any suggestions, I would be grateful.

Many thanks

1 ACCEPTED SOLUTION
Super User

The solution below is assuming that you are importing the data from the SQL Server, and that you are not using DirectQuery otherwise all Solutions will become extremely slow and complex, and may also require using Tabular Editor to create a Calculation Group that holds calculation items: Value1 ... Value5

Importing data from SQL Server

Using PowerQuery ( you can unpivot the value columns:

Your table will look like this:

Still using Power Query you can create a custom column like so:

The formula:

``if [Value] > 0 then "Yes" else "No"``

Now, close and apply Power Query!

You can create a Matrix visual:

Please be aware that I changed the aggregation function to Count after I dragged the Value column to the Values field well.

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
2 REPLIES 2
Helper II

Hello Tom, thank you very much for your easy and well-explained solution! That did the trick and I am smiling from ear to ear. Much appreciate your help! 😁

Super User

The solution below is assuming that you are importing the data from the SQL Server, and that you are not using DirectQuery otherwise all Solutions will become extremely slow and complex, and may also require using Tabular Editor to create a Calculation Group that holds calculation items: Value1 ... Value5

Importing data from SQL Server

Using PowerQuery ( you can unpivot the value columns:

Your table will look like this:

Still using Power Query you can create a custom column like so:

The formula:

``if [Value] > 0 then "Yes" else "No"``

Now, close and apply Power Query!

You can create a Matrix visual:

Please be aware that I changed the aggregation function to Count after I dragged the Value column to the Values field well.

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors