topic Re: Switch statement with numeric what if parameter in DAX Commands and Tips

Switch statement with numeric what if parameter

rcarhart2 — Wed, 13 Nov 2024 22:48:11 GMT

I created a simple numeric what if parameter from 1-12. it is called Date_names
I then want to use a switch statement to display this as:

SWITCH([Date_Names Value],

1, "Last Month vs Prior Month",

2, "Last Quarter vs Prior Quarter",

3, "Last Month YOY",

4, "Last Quarter YOY",

5, "Last Year vs Prior Year",

6, "MTD vs Prior MTD",

7, "QTD vs Prior QTD",

8, "YTD vs Prior YTD",

9, "Rolling 30 Days",

10, "Rolling 12 Months",

11, "Custom",

12, "Last Week YOY"

)

I keep getting an error that the syntax is incorrect for my switch statement. I have tried Date_names and 'Date_Names'[Date_Names Value] in place of Date_names Value. Why is the switch statement not understanding my parameter.

Re: Switch statement with numeric what if parameter

FreemanZ — Thu, 14 Nov 2024 00:17:42 GMT

hi rcarhart2 ,

use the code to create the calculated column.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

Re: Switch statement with numeric what if parameter

rcarhart2 — Thu, 14 Nov 2024 01:02:01 GMT

Hey FreemanZ Thank you for the reply. I am not trying to create a calculated column. Some background I am a tableau user but my company switched to powerBI. I am trying to recreate my tableau logic in powerBI but not sure of the best way to do this. This is the beginning of the logic where I create a parameter then use that logic for dynamic date switching.
for example this is my tableau logic for dates:

Tableau date fields

-------------------
Current Date Period
-------------------
IF [Date Comparison] = 1
OR [Date Comparison] = 3
OR [Date Comparison] = 6
THEN DATEDIFF('month',[Date],[As of Date]) = 0

ELSEIF [Date Comparison] = 2
OR [Date Comparison] = 4
OR [Date Comparison] = 7
THEN DATEDIFF('quarter',[Date],[As of Date]) = 0

ELSEIF [Date Comparison] = 5
OR [Date Comparison] = 8
THEN DATEDIFF('year',[Date],[As of Date]) = 0

ELSEIF [Date Comparison] = 9
THEN DATEDIFF('day',[Date],[As of Date]) < 30
AND [Date] <= [As of Date]

ELSEIF [Date Comparison] = 10
THEN DATEDIFF('month',[Date],[As of Date]) < 12
AND [Date] <= [As of Date]

ELSEIF [Date Comparison] = 11
THEN [From] <= [Date]
AND [To] >= [Date]

ELSEIF [Date Comparison] = 12
THEN DATEDIFF('week',[Date],[As of Date]) = 0
and [Date] <= [As of Date]

END

-----------------------
Partial Month Flag
----------------------
DATETRUNC('month',[Date]) = DATETRUNC('month',{MAX([Date])})

-----------------------
Partial Quarter Flag
-----------------------
DATETRUNC('quarter',[Date]) = DATETRUNC('quarter',{MAX([Date]

-----------------------
Partial Week Flag
----------------------
DATETRUNC('week',[Date]) = DATETRUNC('week',{MAX([Date])})

-----------------------
partial year flag
-----------------------
DATETRUNC('year',[Date]) = DATETRUNC('year',{MAX([Date])})

-----------------------
prior date period
----------------------
IF [Date Comparison] = 1 THEN DATEDIFF('month',[Date], [As of Date]) = 1

ELSEIF [Date Comparison] = 2 THEN DATEDIFF('quarter',[Date], [As of Date]) = 1

ELSEIF [Date Comparison] = 3 THEN DATEDIFF('month',[Date], [As of Date]) = 12

ELSEIF [Date Comparison] = 4 THEN DATEDIFF('quarter',[Date],[As of Date]) = 4

ELSEIF [Date Comparison] = 5 THEN DATEDIFF('year',[Date],[As of Date]) = 1

ELSEIF [Date Comparison] = 6 THEN DATEDIFF('month',[Date] ,[As of Date]) = 1 AND [Date] <= DATEADD('month',-1,[As of Date])

ELSEIF [Date Comparison] = 7 THEN DATEDIFF('quarter',[Date],[As of Date]) = 1 AND [Date] <= DATEADD('quarter',-1,[As of Date])

ELSEIF [Date Comparison] = 8 THEN DATEDIFF('year',[Date],[As of Date]) = 1 AND [Date] <= DATEADD('year',-1,[As of Date])

ELSEIF [Date Comparison] = 9 THEN DATEDIFF('day',[Date],[As of Date]) >= 30 AND DATEDIFF('day',[Date],[As of Date]) < 60

ELSEIF [Date Comparison] = 10 THEN DATEDIFF('month',[Date],[As of Date]) >= 12 AND DATEDIFF('month',[Date],[As of Date]) < 24

ELSEIF [Date Comparison] = 11 THEN DATEDIFF('day',[Date],[As of Date]) >= [Days in Range] AND DATEDIFF('day',[Date],[As of Date]) < [Days in Range]*2

ELSEIF [Date Comparison] = 12 then DATEDIFF('week',[Date],[As of Date]) = 52

END

------------------------------
as of date
------------------------------
//MTD (6), QTD (7), YTD (8), Rolling 30 days (9)
IF [Date Comparison] >= 6 AND [Date Comparison] <= 9

THEN {MAX([Date])}

//Custom (11)
ELSEIF [Date Comparison] = 11

THEN [To]

ELSE
{MAX(

//Last Full vs. Prior Month (1) OR Last Full Month YoY (3) OR Rolling 12 Months (10)
IF (( [Date Comparison] = 1 OR [Date Comparison] = 3 OR [Date Comparison] = 10 )
AND NOT [Partial Month Flag] )

//Last Full vs. Prior Quarter (2) OR Last Full Quarter YoY (4)
OR (( [Date Comparison] = 2 OR [Date Comparison] = 4)
AND NOT [Partial Quarter Flag] )

//Last Full vs. Prior Year (5)
OR (( [Date Comparison] = 5)
AND NOT [partial year flag] )
//Last Full week vs Prior Year (12)
OR (( [Date Comparison] = 12)
AND NOT [Partial Week Flag2] )

THEN [Date] END)}

END

-----------------------------
Days in Range
-----------------------------
DATEDIFF('day',
{MIN( IF [Current Date Period] THEN [Date] END)},
{MAX( IF [Current Date Period] THEN [Date] END)})+1
-----------------------------
Days in range comparison
-----------------------------
DATEDIFF('day',
{MAX( IF [Prior Date Period] THEN [Date] END)},
{MAX( IF [Current Date Period] THEN [Date] END)})

Tableau Date parameters

----------------------
Date comparison
----------------------
Integer: Display as
1: Last Month vs Prior Month
2: Last Quarter vs Prior Quarter
3: Last Month YOY
4: Last Quarter YOY
5: Last Year vs Prior Year
6: MTD vs Prior MTD
7: QTD vs Prior QTD
8: YTD vs Prior YTD
9: Rolling 30 Days
10: Rolling 12 months
11: Custom
12: Last Week YOY

------------------
From
-----------------
Date

----------------
To
---------------
Date

I am trying to recreate the date comparison part in powerBI.

Re: Switch statement with numeric what if parameter

Poojara_D12 — Thu, 14 Nov 2024 06:02:40 GMT

Hi rcarhart2

The issue you’re encountering is likely due to referencing the What-If parameter incorrectly in your SWITCH statement. When you create a What-If parameter in Power BI, it generates a table and a measure associated with that parameter. Typically, the measure representing the parameter's selected value is named something like [ParameterName Value], where "ParameterName" is the name you gave the parameter.

Here’s how to correctly reference the parameter in your SWITCH statement:

Verify Parameter Measure Name: Check the Fields pane in Power BI to confirm the exact name of the measure for your What-If parameter. It should appear under the parameter table with a name like [Date_names Value].
Update the SWITCH Statement: Use this measure in your SWITCH statement as shown below:

SWITCH( [Date_names Value], 1, "Last Month vs Prior Month", 2, "Last Quarter vs Prior Quarter", 3, "Last Month YOY", 4, "Last Quarter YOY", 5, "Last Year vs Prior Year", 6, "MTD vs Prior MTD", 7, "QTD vs Prior QTD", 8, "YTD vs Prior YTD", 9, "Rolling 30 Days", 10, "Rolling 12 Months", 11, "Custom", 12, "Last Week YOY", "Invalid Selection" // Optional default case )

Common Issues to Check:

Correct Measure Name: Ensure [Date_names Value] matches the measure name exactly. It’s case-sensitive, so ensure capitalization is correct.
Data Type: Confirm that [Date_names Value] is numeric (which it should be, as it’s from a What-If parameter).
Default Case: Adding a default case (e.g., "Invalid Selection") at the end can sometimes help troubleshoot unexpected results.

Let me know if this resolves the issue!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Switch statement with numeric what if parameter

rcarhart2 — Thu, 14 Nov 2024 20:39:27 GMT

Poojara_D12 Thank you. I was also doing this in dax when it was a measure. thank you for your reply.

Re: Switch statement with numeric what if parameter

Poojara_D12 — Fri, 15 Nov 2024 04:29:17 GMT

rcarhart2 Glad to hear that!! 🙂