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

[reddy421_hc]

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.

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

[amitchandak]

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

[reddy421_hc]

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.

[v-kelly-msft]

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!