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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataSkills
Helper II
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 NameValue 1Value 2Value 3Value 4Value 5
Acme00132
Buffalo11512
Cincinatti20017
Delta20421
Enron422201
Figaro22000

 

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 1Value 2Value 3Value 4Value 5
Yes53445
No13221


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

 

CompanyMetricValue
AcmeValue 1No
AcmeValue 2No
AcmeValue 3Yes
AcmeValue 4Yes
AcmeValue 5Yes
BuffaloValue 1Yes
BuffaloValue 2Yes
EtcEtcEtc

 

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
TomMartens
Super User
Super User

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:
image.png
Your table will look like this:
image.png
Still using Power Query you can create a custom column like so:
image.png
The formula:

 

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

 


Now, close and apply Power Query!

You can create a Matrix visual:
image.png

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

View solution in original post

2 REPLIES 2
DataSkills
Helper II
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! 😁

TomMartens
Super User
Super User

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:
image.png
Your table will look like this:
image.png
Still using Power Query you can create a custom column like so:
image.png
The formula:

 

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

 


Now, close and apply Power Query!

You can create a Matrix visual:
image.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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