Forum Discussion
Switching Values on a report with a splicer
Hi jcox,
I'm not sure what is the type of data you are analysing but I had the same issue regarding MTD, QTD, YTD values while on my search I have found a DAX webpage that allow me to do this. (sorry for not remebering the website address to be recorded that this is not my solution but I have learned it from others) below is what i do to do a dinamic change of the values based on a slicer. I have used this tecnhic with other type of parameters and worked fine with the proper adjustments.
Lets suposse you have the monthy sales and want to do a YTD, MTD, QTD calculation, and that we have a Sales table and a date table linked to sales.
First create a table with the parameters you want to use for your slicer that as the following structure:
Table Name = Timeframe
ID Timeframe
1 MTD
2 YTD
3 QTD
IMPORTANT: dont' create any relationship with other tables must be a standalone table.
Within the table create the two following measures:
Selected Timeframe= MIN(Timeframe[ID])
Selection = IF (HASONEVALUE ( Timeframe[Timeframe]), VALUES ( Timeframe[Timeframe]))
The first measure gives you the minimum ID selected in the slicer.
The second one gives tells you what is the interval to consider when you select the values in the slicer if none are selected the full table is considered and then the minimum value is used based on the first measure.
On the table of the sales create the following measures:
SALESMTD = TOTALMTD(SUM(Sales[Sales_Value]),Dates[Date])
SALESQTD = TOTALQTD(SUM(Sales[Sales_Value]),Dates[Date])
SALESYTD = TOTALYTD(SUM(Sales[Sales_Value]),Dates[Date])
This makes the calculation for each parameter you have in the Timeframe table. Add also the following measure:
TotalSales= SWITCH([Selected Timeframe],1,[SALESMTD],2,[SALESQTD],3,[SALESYTD],BLANK(),[SALESMTD])
You evaluate the SelectedTimeframe and based on its number you return the corresponding formula, the last part is for you to have a value if no value in the slicer is chosend. This formula (SWITCH) allows to do the same as the IF formula but as more "power" in it (check: https://msdn.microsoft.com/en-us/library/gg492166.aspx and google it and you will find the use of this formula).
Now you can use the Timeframe for your slicer and the TotalSales for you graphs, tables whatever when you change the slicer the calculations change accordingly.
This also works if instead of the MTD, QTD, YTD you replace the measures by Pieces and Value you have to do some measures like:
Table = SalesParameters
ID Parameter
1 Pieces
2 Value
Selected SalesParameter= MIN(SalesParameter[ID])
Selection = IF (HASONEVALUE ( SalesParameter[Parameter]), VALUES ( SalesParameter[Parameter]))
SALESVALUES = SUM(Sales[Sales_Value])
PIECESVALUES = SUM(Sales[Sales_Pieces])
SalesTotal = SWITCH([Selected SalesParameter],1,Sales[PIECESVALUES],2,Sales[SALESVALUES],BLANK(),[PIECESVALUES])
Now if you do the SalesTotal in a graph when you SWITCH the slicer it gives you values or pieces.
Just one important thing is that the switch formula does not allow to have different types of data example numbers and percentages they all have to be in the same format.
Hope this helps and sorry for the long post. Any question please tell me.
MFelix
Hi jcox,
I'm not sure what is the type of data you are analysing but I had the same issue regarding MTD, QTD, YTD values while on my search I have found a DAX webpage that allow me to do this. (sorry for not remebering the website address to be recorded that this is not my solution but I have learned it from others) below is what i do to do a dinamic change of the values based on a slicer. I have used this tecnhic with other type of parameters and worked fine with the proper adjustments.
Lets suposse you have the monthy sales and want to do a YTD, MTD, QTD calculation, and that we have a Sales table and a date table linked to sales.
First create a table with the parameters you want to use for your slicer that as the following structure:
Table Name = Timeframe
ID Timeframe
1 MTD
2 YTD
3 QTD
IMPORTANT: dont' create any relationship with other tables must be a standalone table.
Within the table create the two following measures:
Selected Timeframe= MIN(Timeframe[ID])
Selection = IF (HASONEVALUE ( Timeframe[Timeframe]), VALUES ( Timeframe[Timeframe]))
The first measure gives you the minimum ID selected in the slicer.
The second one gives tells you what is the interval to consider when you select the values in the slicer if none are selected the full table is considered and then the minimum value is used based on the first measure.
On the table of the sales create the following measures:
SALESMTD = TOTALMTD(SUM(Sales[Sales_Value]),Dates[Date])
SALESQTD = TOTALQTD(SUM(Sales[Sales_Value]),Dates[Date])
SALESYTD = TOTALYTD(SUM(Sales[Sales_Value]),Dates[Date])
This makes the calculation for each parameter you have in the Timeframe table. Add also the following measure:
TotalSales= SWITCH([Selected Timeframe],1,[SALESMTD],2,[SALESQTD],3,[SALESYTD],BLANK(),[SALESMTD])
You evaluate the SelectedTimeframe and based on its number you return the corresponding formula, the last part is for you to have a value if no value in the slicer is chosend. This formula (SWITCH) allows to do the same as the IF formula but as more "power" in it (check: https://msdn.microsoft.com/en-us/library/gg492166.aspx and google it and you will find the use of this formula).
Now you can use the Timeframe for your slicer and the TotalSales for you graphs, tables whatever when you change the slicer the calculations change accordingly.
This also works if instead of the MTD, QTD, YTD you replace the measures by Pieces and Value you have to do some measures like:
Table = SalesParameters
ID Parameter
1 Pieces
2 Value
Selected SalesParameter= MIN(SalesParameter[ID])
Selection = IF (HASONEVALUE ( SalesParameter[Parameter]), VALUES ( SalesParameter[Parameter]))
SALESVALUES = SUM(Sales[Sales_Value])
PIECESVALUES = SUM(Sales[Sales_Pieces])
SalesTotal = SWITCH([Selected SalesParameter],1,Sales[PIECESVALUES],2,Sales[SALESVALUES],BLANK(),[PIECESVALUES])
Now if you do the SalesTotal in a graph when you SWITCH the slicer it gives you values or pieces.
Just one important thing is that the switch formula does not allow to have different types of data example numbers and percentages they all have to be in the same format.
Hope this helps and sorry for the long post. Any question please tell me.
MFelix
hello MFelix,
Do you think that its possible to do it if instead of having a flexible "sales " relults, we create a flexible "colums name".
For example :Region Customer Sales Customer A 10 Product B 8 C 4 D 3 E 2 TOTAL 27
In this case, if we choose "Customer" , it shows the sales by customer. If we choose "Region", it shows the sales by Region ...
I don't know if its possible...
ThanksHi Aroth,
If I can understand your question you want to change the "axis" on a visual and not the measures correct? I use this method to change the measure that are being count/sum in the visuals an not for changing the axis in my dashboard I haven't had that need yet, please check the link below since I think this is what you need, I haven't tried it.
https://community.powerbi.com/t5/Desktop/Dynamic-Column-Based-on-Slicer-Selection/m-p/81193#M33888
Regards,
MFelix
Thank you very much for your great answers, which I have employed for one of my reports. But for another report, I need to be able to filtre both Time period (MAT, MQT and Monthly figures) AND Measure (Pieces and Values). So I will need to have two parameter/ slicers: one for Time Period and one for Measure. As the user selects MAT and Values, the results will be different to MQT and Pieces are selected.
I tried your method and unfortunately I don't think HASONEVALUE allows you to have two parameters coexisting from two different tables (one for Time period and one for Measure). Do you happen to know a solution to this?Hi MFelix
Thank you very much for your great answers, which I have employed for one of my reports. But for another report, I need to be able to filtre both Time period (MAT, MQT and Monthly figures) AND Measure (Pieces and Values). So I will need to have two parameter/ slicers: one for Time Period and one for Measure. As the user selects MAT and Values, the results will be different to MQT and Pieces are selected.
I tried your method and unfortunately I don't think HASONEVALUE allows you to have two parameters coexisting from two different tables (one for Time period and one for Measure). Do you happen to know a solution to this?Many thanks in advance and much appreciated!
Hi NAnalytics,
I'm assuming that the MAT MQT and Monthly figures will be changes in the result and not on the axis as in the previous part of this post.
The option is to add a switch statment within your other measure that selects the values based on the slicer of the monthly numbers so you would have part of the measure thar would be the x-axis and within that the measure at the monthly numbers.
Can you share the measures you use so I can help you change it?
regards,
Mfelix
Thanks MFelix,
I can't seem to load excel file, so here is what my data columns looks like:
Accounts | Location | MTD values ($) | MTD Services |
I have created calculations for MQT values ($), MAT values ($), YTD values ($), MQT Services, MAT Services, YTD Services as your kind suggestions above.
With your previous posts, I can do a very simple slicer/parameter to switch between MTD value, MQT valueand MAT valueas. Unfortunately I want two slicers/ paramters/ filtres on the report:
- Measure (switch between Value($) and Services)
- Period (switch bewteen MTD, MQT, YTD and MAT)
The graph could be just a simple line graph with time on the X-axis and Measure on the Y-axis. If a user selects:
- Value ($) AND MTD, the graph will polot MTD Value ($) against time on the graph, or
- Value ($) AND MAT, the graph will polot MAT Value($) against time on the graph, or
- Services AND MTD, the graph will polot MTD Services against time on the graph,
- and so on.
I can't seem to find a solution to this for PowerBI, while for Tableau you just add to Parameter and then created a statement such as =IF(AND(Measure="Values", Period="MTD"), sum(MTD Values $), ELSEIF(AND(Measure="Values", Period="MTD"), sum(MTD values $) etc.
By using the HASONEVALUE to create two parameters was ok. But then I click on one slicer, it filtred the other slicer/ parameter, so if I click on MAT, I can only see Services in the Measure slicer with the Value selection disappearing.
Many thanks in advance and much appreciated!
