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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
reddy421_hc
Frequent Visitor

Help in Finding out to show from what source/Sources the data is coming from

Hello All,
I have a dashboard that shows where the data is coming from.
I have a SQL query and that is the source for my Dashboard.
The SQL Query has an attribute that says where the data is coming from.
For Example, if a record is coming from Source a and source b then I am populating that column with a&b.
But I got this by writing a case statement.

With the number of sources Increasing, It is hard for me to write the case statement as the permutations and combinations are increasing.
Is there a way in SQL or PowerBI to make it easier.
Any help will be really appreciated.

 

 

1.jpg

 

 

FYI... My SQL Attribute Case Statement.

 

,CASE WHEN a.CustID IS NOT NULL AND b.[CustID] IS NULL AND c.CustID IS NULL AND d.[CustID] IS NULL AND e.CustID IS NULL THEN 'Source1'
WHEN b.[CustID] IS NOT NULL AND a.CustID IS NULL AND c.CustID IS NULL AND d.[CustID] IS NULL AND e.CustID IS NULL THEN 'Source2'
WHEN c.CustID IS NOT NULL AND a.CustID IS NULL AND b.[CustID] IS NULL AND d.[CustID] IS NULL AND e.CustID IS NULL THEN 'Source3'
WHEN d.[CustID] IS NOT NULL AND a.CustID IS NULL AND b.[CustID] IS NULL AND c.CustID IS NULL AND e.CustID IS NULL THEN 'Source4'
WHEN e.CustID IS NOT NULL AND a.CustID IS NULL AND b.[CustID] IS NULL AND c.CustID IS NULL AND d.[CustID] IS NULL THEN 'Source5'
-----Level2 Start
WHEN a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND c.CustID IS NULL AND d.[CustID] IS NULL AND e.CustID IS NULL THEN 'Source1&Source2'
WHEN a.CustID IS NOT NULL AND c.CustID IS NOT NULL AND b.[CustID] IS NULL AND d.[CustID] IS NULL AND e.CustID IS NULL THEN 'Source1&Source3'
WHEN a.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND b.[CustID] IS NULL AND c.CustID IS NULL AND e.CustID IS NULL THEN 'Source1&Source4'
WHEN a.CustID IS NOT NULL AND e.CustID IS NOT NULL AND b.[CustID] IS NULL AND c.CustID IS NULL AND d.[CustID] IS NULL THEN 'Source1&Source5'

WHEN b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL AND a.CustID IS NULL AND d.[CustID] IS NULL AND e.CustID IS NULL THEN 'Source2&Source3'
WHEN b.[CustID] IS NOT NULL AND d.[CustID] IS NOT NULL AND a.CustID IS NULL AND c.CustID IS NULL AND e.CustID IS NULL THEN 'Source2&Source4'
WHEN b.[CustID] IS NOT NULL AND e.CustID IS NOT NULL AND a.CustID IS NULL AND c.CustID IS NULL AND d.[CustID] IS NULL THEN 'Source2&Source5'

WHEN c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND a.CustID IS NULL AND b.[CustID] IS NULL AND e.CustID IS NULL THEN 'Source3&Source4'
WHEN c.CustID IS NOT NULL AND e.CustID IS NOT NULL AND a.CustID IS NULL AND b.[CustID] IS NULL AND d.[CustID] IS NULL THEN 'Source3&Source5'

WHEN d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL AND a.CustID IS NULL AND b.[CustID] IS NULL AND c.CustID IS NULL THEN 'Source4&Source5'
----Level2 End
----Level3 Start--Just Flipped the NOT NULL TO NULL and NULL to NOT NULL in Level2
WHEN a.CustID IS NULL AND b.[CustID] IS NULL AND c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL THEN 'Source3&Source4&Source5'
WHEN a.CustID IS NULL AND c.CustID IS NULL AND b.[CustID] IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL THEN 'Source2&Source4&Source5'
WHEN a.CustID IS NULL AND d.[CustID] IS NULL AND b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL AND e.CustID IS NOT NULL THEN 'Source2&Source3&Source5'
WHEN a.CustID IS NULL AND e.CustID IS NULL AND b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL THEN 'Source2&Source3&Source4'

WHEN b.[CustID] IS NULL AND c.CustID IS NULL AND a.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL THEN 'Source1&Source4&Source5'
WHEN b.[CustID] IS NULL AND d.[CustID] IS NULL AND a.CustID IS NOT NULL AND c.CustID IS NOT NULL AND e.CustID IS NOT NULL THEN 'Source1&Source3&Source5'
WHEN b.[CustID] IS NULL AND e.CustID IS NULL AND a.CustID IS NOT NULL AND c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL THEN 'Source1&Source3&Source4'

WHEN c.CustID IS NULL AND d.[CustID] IS NULL AND a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND e.CustID IS NOT NULL THEN 'Source1&Source2&Source5'
WHEN c.CustID IS NULL AND e.CustID IS NULL AND a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND d.[CustID] IS NOT NULL THEN 'Source1&Source2&Source4'

WHEN d.[CustID] IS NULL AND e.CustID IS NULL AND a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL THEN 'Source1&Source2&Source3'
----Level3 End
----Level4 Start
WHEN a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NULL THEN 'Source1&Source2&Source3&Source4'
WHEN a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL AND e.CustID IS NOT NULL AND d.[CustID] IS NULL THEN 'Source1&Source2&Source3&Source5'
WHEN a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL AND c.CustID IS NULL THEN 'Source1&Source2&Source4&Source5'
WHEN a.CustID IS NOT NULL AND c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL AND b.[CustID] IS NULL THEN 'Source1&Source3&Source4&Source5'
WHEN b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL AND a.CustID IS NULL THEN 'Source2&Source3&Source4&Source5'
----Level4 End
----Level5 Start
WHEN a.CustID IS NOT NULL AND b.[CustID] IS NOT NULL AND c.CustID IS NOT NULL AND d.[CustID] IS NOT NULL AND e.CustID IS NOT NULL THEN 'Source1&Source2&Source3&Source4&Source5'
----Level5 End
END AS RecordFrom

3 REPLIES 3
amitchandak
Super User
Super User

@reddy421_hc , You can use the switch or Switch true

https://docs.microsoft.com/en-us/dax/switch-function-dax

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

How will that fetch me?

I am still going to write the same big query right?

FYI... Adding my Case statement to my original email for reference.

Hi @reddy421_hc

 

Can you filtering the columns after loading all the data to the desktop,thus you can do the filter simply in power query or table view,no need to write a long query...

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.