Forum Discussion
IF Statement with Slicer and Calculated Column
Apologies, I'm on the newer side of Power BI so still becoming acclimated with its quirks. I am having difficulty understanding how to use the SELECTEDVALUE function properly.
I have a slicer that I am using with a Date Field. I want to filter my data by the currently selected month, and also show the previous month's data. I thought I'd be able to do this easily with a Calculated Column and an IF Statement.
It looks like this:
FILTERDATES =
VAR SelectedMonth = SELECTEDVALUE(Table[Month])
VAR CurrentMonth = MONTH(TODAY())
VAR PreviousMonth = MONTH(DateAdd(TODAY(), -1, MONTH))
RETURN
IF(YEAR(SelectedMonth)=YEAR(TODAY()) && SelectedMonth=CurrentMonth, "Current Month",
IF(SelectedMonth = PreviousMonth && YEAR(SelectedMonth) = YEAR(TODAY()), "Previous Month", "Past"))
I think I am not understanding how to apply SelectedValue correctly. I read that it must be a distinct value, but I assumed it was since in the slicer one can only choose one value at a time? Is it not supposed to filter the data by the selected value? Do I need to make a separate table with just month numbers and attempt to filter that way so the value is distinct?
Thanks
iLoatheMSDeep Calculated columns are not dynamic, they are only calculated when you load data. Therefore, you cannot use a calculated column in conjunction with a slicer. Instead, you will need to use a measure. I don't see anything in your calculated column that would prevent it from being a measure as currently written.
Hi iLoatheMSDeep,
Yes, you got the idea mostly right. The SELECTEDVALUE function gives one value only when a single month is selected in the slicer. If multiple months are selected or “Select All” is used, it will return blank that’s why you are getting empty results.
Also, measures in Power BI work based on the visual’s filter context, not for each row like a calculated column. So the IF condition will check for the selected month in the visual, not row by row.
Try keeping your slicer in single-select mode, and it should start showing the correct text values.
Hope this helps if you have any queries we are happy to assist you further.
Regards,
Community Support Team.
5 Replies
- GeraldGEmerick
Memorable Member
iLoatheMSDeep Calculated columns are not dynamic, they are only calculated when you load data. Therefore, you cannot use a calculated column in conjunction with a slicer. Instead, you will need to use a measure. I don't see anything in your calculated column that would prevent it from being a measure as currently written.
Ah thank you so much for the clarification; I'll keep that in mind going forward. I did try this as a measure, however, I am still getting blank values where I should be getting a text value. So that's why I assumed I didn't understand the 'SELECTEDVALUE' function properly. I believe I read that if there are multiple values in the table with that same data, that it will render blank. How I interpreted it though was that the user selects an option from the slicer, that one option is the Selected Value and the IF statement would be applied to all data in the table that matched the Selected Value. Apparently, that's not how it is supposed to work?
Hi iLoatheMSDeep,
Yes, you got the idea mostly right. The SELECTEDVALUE function gives one value only when a single month is selected in the slicer. If multiple months are selected or “Select All” is used, it will return blank that’s why you are getting empty results.
Also, measures in Power BI work based on the visual’s filter context, not for each row like a calculated column. So the IF condition will check for the selected month in the visual, not row by row.
Try keeping your slicer in single-select mode, and it should start showing the correct text values.
Hope this helps if you have any queries we are happy to assist you further.
Regards,
Community Support Team.
