cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User IV
Super User IV

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors