Forum Discussion
Query from Service Pack using SummarizeColumns and add a column with Switch (Case When in SQL)
Let me elaborate on the question, below will find an SQL and DAX intended view which is not working as the intent is to have less rows imported..
SQL
With PreTable as (
Select
'Geo Hierarchy'[Country],'Prod Hierarchy'[Prd Lvl 1],'Prod Hierarchy'[Prd Lvl 2],
case when 'Prod Hierarchy'[Prd Lvl 3] like '%crawl%' then 'crawl'
when 'Prod Hierarchy'[Prd Lvl 3] like '%walk%' then 'walk'
when 'Prod Hierarchy'[Prd Lvl 3] like '%run%' then 'run'
else 'Others'
end [NewLvl3],
SUM([TotalRev]) as [Rev]
from ServicePack
where 'Calendar'[Year]='2019'
Group by
'Geo Hierarchy'[Country],'Prod Hierarchy'[Prd Lvl 1],'Prod Hierarchy'[Prd Lvl 2],'Prod Hierarchy'[Prd Lvl 3]
)
Select
'Geo Hierarchy'[Country],'Prod Hierarchy'[Prd Lvl 1],'Prod Hierarchy'[Prd Lvl 2],[NewLvl3],SUM([Rev])[Rev]
from PreTable
Group by
'Geo Hierarchy'[Country],'Prod Hierarchy'[Prd Lvl 1],'Prod Hierarchy'[Prd Lvl 2],[NewLvl3]
DAX (not working)
EVALUATE
SUMMARIZECOLUMNS (
'Geo Hierarchy'[Country],'Prod Hierarchy'[Prd Lvl 1],'Prod Hierarchy'[Prd Lvl 2],
SWITCH(SELECTEDVALUES('Prod Hierarchy'[Prd Lvl 3]),
"crawl","crawl",
"run","run"
"Others"
),
FILTER (
VALUES ( 'Calendar'[Year]),
'Calendar'[Year] IN { "2019"}
),
"Rev", [TotalRev]
)
- Anonymous4 years agoNot applicable
Hi juanroblesoct9,
Can you please share some dummy data and table relationship mappings? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
For the switch parts, you can try like these:
SWITCH ( TRUE (), SEARCH ( "crawl", 'Prod Hierarchy'[Prd Lvl 3], 1, BLANK () ), "crawl", SEARCH ( "run", 'Prod Hierarchy'[Prd Lvl 3], 1, BLANK () ), "run", "Others" )From SQL to DAX: String Comparison - SQLBI
Regards,
Xiaoxin Sheng