Forum Discussion
case when with between two dates - HOW TO USE IN DAX?
Hello guys, I have this case when with between two dates, how can I do this in dax? Can someone help me?
WHEN DATE(leads.created_at) between '2020-01-01' and '2020-02-29' THEN '1ªB'
WHEN DATE(leads.created_at) between '2020-03-01' and '2020-04-30' THEN '2ªB'
WHEN DATE(leads.created_at) between '2020-05-01' and '2020-06-30' THEN '3ªB'
WHEN DATE(leads.created_at) between '2020-07-01' and '2020-08-31' THEN '4ªB'
WHEN DATE(leads.created_at) between '2020-09-01' and '2020-10-31' THEN '5ªB'
WHEN DATE(leads.created_at) between '2020-11-01' and '2020-12-31' THEN '6ªB'
WHEN DATE(leads.created_at) between '2019-01-01' and '2019-02-28' THEN '1ªB'
WHEN DATE(leads.created_at) between '2019-03-01' and '2019-04-30' THEN '2ªB'
WHEN DATE(leads.created_at) between '2019-05-01' and '2019-06-30' THEN '3ªB'
WHEN DATE(leads.created_at) between '2019-07-01' and '2019-08-31' THEN '4ªB'
WHEN DATE(leads.created_at) between '2019-09-01' and '2019-10-31' THEN '5ªB'
WHEN DATE(leads.created_at) between '2019-11-01' and '2019-12-31' THEN '6ªB'
WHEN DATE(leads.created_at) between '2021-01-01' and '2021-02-28' THEN '1ªB'
WHEN DATE(leads.created_at) between '2021-03-01' and '2021-04-30' THEN '2ªB'
WHEN DATE(leads.created_at) between '2021-05-01' and '2021-06-30' THEN '3ªB'
WHEN DATE(leads.created_at) between '2021-07-01' and '2021-08-31' THEN '4ªB'
WHEN DATE(leads.created_at) between '2021-09-01' and '2021-10-31' THEN '5ªB'
WHEN DATE(leads.created_at) between '2021-11-01' and '2021-12-31' THEN '6ªB'
ELSE null end as 'bimestre',
Here's a reference and a sample. This should do if you expand it with the other categories and appropriate result values:
Test = SWITCH(TRUE(), dimDate[Date]>=DATE(2020,01,01) && dimDate[Date]<DATE(2020,02,29),"1ªB", "N/A")Best regards,
Tim
5 Replies
- Pragati11
Super User
Hi bocciawinsocial ,
You can try writing a measure using DAX with SWITCH function. Refer an example in the following:
https://radacad.com/write-conditional-statement-using-switch-in-dax-and-power-bi
Thanks,
Pragati
- timg
Solution Sage
Here's a reference and a sample. This should do if you expand it with the other categories and appropriate result values:
Test = SWITCH(TRUE(), dimDate[Date]>=DATE(2020,01,01) && dimDate[Date]<DATE(2020,02,29),"1ªB", "N/A")Best regards,
Tim
Thanks timg,
do you know how to solve this: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.- timg
Hi bocciawinsocial,
Could you check if you are refering to the datecolumn or the date table in your code? The spots where I mention dimDate[Date] refer to the specific date column within the table. if you only mention the table (for example 'dimDate') this may cause the error, since there are multiple columns within the table.
smpa01Community Champion
bocciawinsociallike this
SWITCH(TRUE(), DATE(leads.created_at) >= '2020-01-01' && DATE(leads.created_at) <='2020-02-29', '1ªB', DATE(leads.created_at) >= '2020-03-01' && DATE(leads.created_at) <='2020-04-30', '2ªB', .....................................................................................)
