Han Solo’s Smugglers Guide to Power BI: Case Constructs Using SWITCH
In the last three posts on #SmugglersGuidetoPowerBI we have been exploring the outer rim of Power BI to perform programming type constructs; i.e., implementing “pseudo-for loops” and/or “pseudo-while loops”. This led to how we can use “hidden compartments” utilizing Parameters for user input. There we saw how to manipulate the GENERATESERIES function that Power BI generates when you create a New Parameter.
?
For my exploration I used the New Parameter GENERATESERIES to present a range of dates the user can use as input to select the desired Time Series start date and ending date to fine-tune forecasting solutions. The data I am using is collected monthly; therefore, I do not have data for each day. The problem is that GENERATESERIES in a Parameter can easily perform a “pseudo-for loop” to generate the user input selections by day, but GENERATESERIES cannot group data points by month for the user input Parameter requirement. I will also look at ‘Field Parameters’ for this solution in another post, but I do want to continue to explore the use of GENERATESERIES in a numeric series in this post.
?
One way I could do this is by using the programming construct, which is a Case Control Structure. In Power BI this can be performed using the SWITCH function. To accomplish this requirement, I added a new column to my Parameters, ‘Time Series Start Date’ and ‘Time Series End Date’, which will contain my “Case Structure”; i.e., the SWITCH function.
?
Side Note
GENERATESERIES can be used to create a date table; e.g., of only MM/<1st day of the month>/YYYY. This can be achieved as follows. To following statement will generate a date table of 10 years; the 120 in the formula is 12 months x 10 years, and the starting year is 2021.
?
DateTable =
? GENERATE (
? ? GENERATESERIES(1,120),
? ? VAR inc = [Value]
? RETURN ROW (
? ? "Date", DATE(2021,inc,1)
? ? )
? )
?
Now back to the SWITCH function…
Microsoft Learn Notes the following concerning SWITCH:
?
Evaluates an expression against a list of values and returns one of multiple possible result expressions. This function can be used to avoid having multiple nested?IF?statements.
Syntax
SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])?
?
Remarks
·?????? The?expression?to be evaluated can be a constant value or an expression. A common use of this function is to set the first parameter to TRUE. See examples below.
·?????? All?result?expressions and the?else?expression must be of the same data type.
·?????? The order of conditions matters. As soon as one?value?matches, the corresponding?result?is returned, and other subsequent?values?aren’t evaluated. Make sure the most restrictive?values?to be evaluated are specified before less restrictive?values. See examples below.
?
The Parameter GENERATESERIES works with numeric data and not with dates; therefore, as shown in the previous post, I used the date serial numbers to provide the users date selections. To convert the daily date serial numbers to a single date for each month I implemented the following SWITCH formula. The user input selection dates start on April 2021 and end (for now) on March 2024 (the date range of the source data). The statement to perform the conversion of the user input is:
?
Time Series Start Date Switch =
Switch ( True(),
[Time Series Start Date]<=44316,44287,
[Time Series Start Date]<=44347,44317,
[Time Series Start Date]<=44377,44348,
[Time Series Start Date]<=44408,44378,
…
[Time Series Start Date]<=45565,45536,
[Time Series Start Date]<=45596,45566,
[Time Series Start Date]<=45626,45597,
领英推荐
[Time Series Start Date]<=45657,45627,
45627)
?
Note
It doesn’t matter that the SWITCH statement ends with future dates. As shown in the last post, the GENERATESERIES function will only generate existing dates from the source data.
?
The way we manipulated the Parameter in the previous post was:
?
Time Series Start Date =
?GENERATESERIES(Value(MIN('PRD Disk PP History'[Date Serial Number])),
??????????????? ?????????????????Value(MAX('PRD Disk PP History'[Date Serial Number])), 1)
?
The GENERATESERIES syntax is:
GENERATESERIES(<startValue>, <endValue>[, <incrementValue>])
?
I added a screen shot for reference to the Parameters utilizing GENERATESEIES from the previous post: ‘Time Series Start Date’; and ‘Time Series End Date’. In the screen shot I am showing you that I added the SWITCH statement directly to the two Parameters.
?
The date serial numbers in the SWITCH formula work out as follows:
?
[Time Series Start Date]<=44316,44287,????????????????? Dates <= 4/30/2021 will return 4/2021
[Time Series Start Date]<=44347,44317, ???????????????? Dates <= 5/31/2021 will return 5/2021
[Time Series Start Date]<=44377,44348, ???????????????? Dates <= 6/30/2021 will return 6/2021
…
?
Note
Again, the GENERATESERIES Parameter sets the dates from the start date of the source data to the end date of the source data; therefore, I do not need to add additional logic here for dates less than 44287 (4/1/2021) or greater than 45382 (3/31/2024). Also, Power BI will technically make all the dates representing 4/2021 as 4/1/2021. All I need is for any date in 4/2021 to evaluate to 4/2021 (even if it is technically 4/1/2021). Making all the dates like 4/2/2024 to be 4/2024 is easily handled through formatting.
?
Why change all the dates in the month to the first day of the month? The source data is a group of Excel spreadsheets in a folder. In Power BI I selected ‘Get Data’ -> ‘Folder’. Power BI adds a column to the source data when you use this method to get your data. The column is named ‘Source.Name’, and it contains the file name of each spreadsheet in relation to all the rows being present in each spreadsheet. In my case the file name of each spreadsheet has the month and year at the same location in the filename. I simply added a column to my source data in Power Bi named ‘Date’. I used a formula like the following to get the date:
?
Date = MID('PRD Disk PP History'[Source.Name],19,2)&"/"&MID('PRD Disk PP History'[Source.Name],14,4)
?
I could have added EOMONTH to easily include the last day of the month in my ‘Date’ column, and I could have worked with all my data being dated as the last day of the moth (and I could have adjusted the SWITCH as such); however, I just didn’t need it as I only need to know the month and year. The reason the dates in my date column are always the first day of the month is because Power BI makes this assumption based on the data, which is in MM/YYYY and not MM/DD/YYYY. Therefore, I convert all dates in a month to the first day of the month in my SWITCH statement. This works as I need it to work (with formatting), so I have not bothered to change it. The link below has a nice demonstration of how to use the EOMONTH function:
?
?
While exploring the outer rim we have found one way to configure user inputs. In this case the user inputs are to select Time Series dates for forecasting and analysis. Utilizing Power BI to work with real programming constructs requires the smuggler’s abilities to talk our way out of trouble, or into a better deal. It also requires a little bit of basic ship maintenance. As one smuggler once said, ”I just got this bucket back together.? I'm not going to let something tear it apart.”
?
In tribute to the day this was posted:
?