Using Parameter/Slicer in Calculated column to change the view of x-axis.

[powerbi_fanatic]

Hi,

I am very new to Power BI and have been an advanced user of Tableau. I am trying to replicate something I did all the time in Tableau.

I want to create custom parameters where I predefine a list of values for users to choose. Based on the chosen value, a dimension/column value changes. For example, a user is given two values to choose from - Monthly and Yearly. 

I have a trend graph that shows Date on x-axis. And total sales on y-axis. On choosing Monthly, the date axis changes to a monthly view and on choosing yearly, the data axis changes to a yearly view. 

In Tableau I have done by creating a parameter with 'yearly and monthly' values. I have create a calculated field to calculate the date column.

CASE <parameter>

WHEN 'Monthly' THEN MONTH(<Date>)

WHEN 'Yearly' THEN YEAR(<Date>)

END

By putting this new calculated column on x-axis, I am able to switch between two views. 

I would like to replicate a similar functionality using PowerBI. 

Thanks in advance.

[andre]

you need to create a table with a column that contains your parameter values, then create a slicer from that column.  Now you can create a measure that uses that slicer value using the following pseudo code:

if(hasonevalue(mytable[myparameter]), if(values(mytable[myparameter]="a", "a", ...)

if hasonevalue() returns True then only one parameter value is selected and the VALUES() function can be used to read what that selected parameter is.

[powerbi_fanatic]

Thanks andre for the reply. I tried your solution, however, not getting the desired result. 

Here's the DAX function I wrote - 

Column = if (HASONEVALUE('Chart View'[Parameter View]), IF(VALUES('Chart View'[Parameter View]) = "Monthly","M", IF(VALUES('Chart View'[Parameter View]) = "Yearly", "Y", "N")),"A")

I have created a new table called [Parameter View] having two values - "Monthly" and "Yearly". If the function above works correctly, I would like the following results.

On selecting "Monthly" from the slicer, the calculated column should display "M".

On selecting "Yearly" from the slicer, the calculated column should display "Y".

Right now, i am getting "A" for any selection.

Appreciate your help.

Thanks.

[v-shex-msft]

Hi @powerbi_fanatic,

In my opinion, you can use format function and switch function to achieve your requirement.

Steps:

1. Add a unit table to store the unit.

Table formula:
Unit = UNION(ROW("Unit","Year"),ROW("Unit","MonthNo"),ROW("Unit","Day"),ROW("Unit","DayOfWeek"),ROW("Unit","Month"))

2. Add a measure to get the select it from unit table.

Select Item = if(HASONEVALUE(Unit[Unit]),VALUES(Unit[Unit]),BLANK()) 

3. Add measure to calculate the display format based on select item.

Dynamic Result = 
var currentDate=MAX([Date])
var para=SWITCH([Select Item],"Year","yyyy","MonthNo","m","Day","d","DayOfWeek","dddd","Month","mmmm","mm/hh/yyyy") 
return
FORMAT(currentDate,para)

4. Drag date field and measure to table visual, use the unit field to create a slicer.

5. Select the item on slicer to switch the display value.

Regards,

Xiaoxin Sheng