Forum Discussion
Using SWITCH with CALCULATE?
I'm new to Power BI so I apologise in advance if there's something obvious that I've missed.
I have a couple of measures which sum values within a specific date range, to allow comparing between two date ranges on the same column chart.
The original measure code I had was:
HH17 2018-2019 = CALCULATE(SUM('HH Consumption Report'[Consumption(KWh) HH17]),'HH Consumption Report'[Date] >= DATE(2018,9,1) && 'HH Consumption Report'[Date] <= DATE(2019,9,1))
This worked great as far as it went, but now I want to generalise it so that the column being looked at can be selected dynamically using a table (because I couldn't find anything that does a dropdown list box).
Trying to put the necessary SWITCH function call inside the SUM did not work, so what I'm trying now is I've unpivoted the data (so instead of columns named "Consumption(KWh) HH17" and so on, these are now values in a column called "Attribute") but I still don't seem to be able to use the new Attribute column together with a SWITCH function call in the CALCULATE conditions.
I've been trying the code below, but Power BI refuses to accept it, giving an error "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed".
First HH 2018-2019 = CALCULATE(SUM('HH Consumption Report'[Value]),'HH Consumption Report'[Date] >= DATE(2018,9,1) && 'HH Consumption Report'[Date] <= DATE(2019,9,1) && 'HH Consumption Report'[Attribute] = SWITCH('HH Consumption Report'[Selected Measure],1,"Consumption(KWh) HH1",3,"Consumption(KWh) HH3",5,"Consumption(KWh) HH5",7,"Consumption(KWh) HH7",9,"Consumption(KWh) HH9",11,"Consumption(KWh) HH11",13,"Consumption(KWh) HH13",15,"Consumption(KWh) HH15",17,"Consumption(KWh) HH17",19,"Consumption(KWh) HH19",21,"Consumption(KWh) HH21",23,"Consumption(KWh) HH23",25,"Consumption(KWh) HH25",27,"Consumption(KWh) HH27",29,"Consumption(KWh) HH29",31,"Consumption(KWh) HH31",33,"Consumption(KWh) HH33",35,"Consumption(KWh) HH35",37,"Consumption(KWh) HH37",39,"Consumption(KWh) HH39",41,"Consumption(KWh) HH41",43,"Consumption(KWh) HH43",45,"Consumption(KWh) HH45",47,"Consumption(KWh) HH47"))
Can anyone suggest what I'm doing wrong?
Here is the code for [Selected Measure]:
Selected Measure = SELECTEDVALUE('Measure Selection'[Code],1)
And 'Measure Selection' is a table that looks like this:
| Hour | Code |
| 00:00 - 01:00 | 1 |
| 01:00 - 02:00 | 3 |
| 02:00 - 03:00 | 5 |
| 03:00 - 04:00 | 7 |
| 04:00 - 05:00 | 9 |
| 05:00 - 06:00 | 11 |
| 06:00 - 07:00 | 13 |
| 07:00 - 08:00 | 15 |
| 08:00 - 09:00 | 17 |
| 09:00 - 10:00 | 19 |
| 10:00 - 11:00 | 21 |
| 11:00 - 12:00 | 23 |
| 12:00 - 13:00 | 25 |
| 13:00 - 14:00 | 27 |
| 14:00 - 15:00 | 29 |
| 15:00 - 16:00 | 31 |
| 16:00 - 17:00 | 33 |
| 17:00 - 18:00 | 35 |
| 18:00 - 19:00 | 37 |
| 19:00 - 20:00 | 39 |
| 20:00 - 21:00 | 41 |
| 21:00 - 22:00 | 43 |
| 22:00 - 23:00 | 45 |
| 23:00 - 00:00 | 47 |
This was based on ideas from this article: https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
I've cracked it. The solution was to add Fiscal year to the "Legend" of the clustered column chart. This then has the result I wanted.
5 Replies
LRSFC_DanJ , You can consider SUMX
Old measure slicer technique
measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/Or field parameter https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9afd
amitchandak Field parameters sounds like what I wanted, but I don't seem to be able to make it work with SUM?
I tried to do this:
Selected 2018-2019 = CALCULATE(SUM('Selected half-hours to compare'[Selected half-hours to compare]), 'HH Consumption Report 2018-2022'[Date] >= DATE(2018,9,1) && 'HH Consumption Report 2018-2022'[Date] <= DATE(2019,9,1))But it does not work, it reports an error saying that SUM cannot work with a string.
("Selected half-hours to compare" is a Field parameter)
I was able to make some progress, I created calculated measures which added together the two half-hours which represent each hour, and was able to use these in a field parameter, which produced the following:
However, I want to be able to have bars for different years side-by-side, ideally selectable using another slicer. And these are not calendar years but financial/academic years, i.e. they run from September to August. Not sure how I can accomplish this?
Following advice at https://community.powerbi.com/t5/Desktop/DAX-Formulas-for-Fiscal-Year-Fiscal-Quarter-and-Month-Short-Name/m-p/18268 I was able to create columns for Month, Year and Fiscal Year as follows:
Month = MONTH('HH Consumption Report 2018-2022'[Date]) Year = YEAR('HH Consumption Report 2018-2022'[Date]) Fiscal Year = IF('HH Consumption Report 2018-2022'[Month] >= 9, CONVERT('HH Consumption Report 2018-2022'[Year], STRING), CONVERT('HH Consumption Report 2018-2022'[Year] - 1, STRING))Adding another slicer now allows me to select a single fiscal year to display. However I can't figure out how to make it show two fiscal years side by side - selecting multiples in the slicer just displays them added together.
Any idea how I can have multiple fiscal years side by side?
I've cracked it. The solution was to add Fiscal year to the "Legend" of the clustered column chart. This then has the result I wanted.
