Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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/
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...
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |