Switching Values on a report with a splicer

[jcox]

I'm looking to find a way that I can switch between two different sets of values with a slicer. I've tried a couple methods that have worked by appending a query and setting an identifier with it, and slicing it that way. But the problem with that is I'm loading the same set of data twice and it slows down my refresh times. 

If I could find a way that would be able switch between 2 sets of values with less of a slicer type function, but more of a button type function that would take values from one column for one selection, and another column's values for the other selection. 

I know this isn't a direct function in Power Bi currently, but wondering if there is a way that I could emulate this functionality.

Thanks for your time.

-Jonathon

[dmcdmc]

Hey there! I know it's a little while later, but trying to implement this as a new PBi user and struggling, hoping to get some help.

I've implemented everything you said so,

1. Made a timeframe table with just two periods

with measures

Selected Timeframe = MIN(Timeframe[ID])

Selection = IF(HASONEVALUE(Timeframe[Timeframe]), VALUES(Timeframe[Timeframe]))

2. I'm looking at cost and budget right now. Somehow, the budget works but the cost does not. Here is what I have for measures in my main dataset called 'SAMPLE', and date is in month-year format

COSTQTD_S = TOTALQTD(SUM('SAMPLE'[Actual]),'SAMPLE'[DATE])

COSTYTD_S = TOTALYTD(SUM('SAMPLE'[Actual]),'SAMPLE'[DATE])

BUDQTD_S = TOTALQTD(SUM('SAMPLE'[Remaining Budget]),'SAMPLE'[DATE])

BUDYTD_S = TOTALYTD(SUM('SAMPLE'[Remaining Budget]),'SAMPLE'[DATE])

NOTE: my dataset comes with 'actual' and 'budget' columns, and I created a new column called 'remaining budget' that is just [budget] - [actual]

with these I made 

Total Cost S = SWITCH([Selected Timeframe], 1, [COSTYTD_S], 2, [COSTQTD_S], BLANK(), [COSTYTD_S])

Total R Budget S = SWITCH([Selected Timeframe], 1, [BUDYTD_S], 2, [BUDQTD_S], BLANK(), [BUDYTD_S])

Now, I have tried making a series of visualizations and for some reason, Total Cost S on QTD just does not work whereas Total R Budget is totally fine in QTD and YTD view. Let me know if there is any additional information I can provide for clarity. Thanks a lot!

[MFelix · Accepted solution]

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

[NAnalytics]

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!

[MFelix]

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

[NAnalytics]

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!

[MFelix]

Can you share the excel file by a link to onedrive or google drive?

Regards,

MFelix

[NAnalytics]

Thanks @MFelix,

Please find a sample of data here, which contains 2 locations, 2 accounts, time, values and units/pieces.

So I just would like to create MAT, MQT and YTD for each of the values and units/pieces. Then have one filtres/ slicer for Period (MTD, YTD, MQT vs MAT) and one for Measure (values vs units/pieces).

Many thanks and hope it's clear.

Looking forward to hearing from you.

[NAnalytics]

@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?

[Aroth]

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...

Thanks

[MFelix]

Hi @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