Best model to achieve objective

[DataSkills]

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

[DataSkills]

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

[TomMartens · Accepted solution]

Hey @DataSkills ,

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