Reply
Frequent Visitor
Posts: 8
Registered: ‎01-11-2016

Slice Facts based on UI events

Folks:

I have a problem that I have recreated in the attached .pbix file.  This file contains only AdventureWorks data.

 

AdventureWorks data can be looked at based on OrderDate, DueDate or ShipDate.  (Only one of these dates can be related to DimDate.  In my model it is OrderDate.  The other two relationships are inactive.)

 

If you look at the UI – I have a slicer where you can change the particular date you want to use to look at the data.  The slicer works.  The table view above the slicer will change as you click on various buttons.

 

So – in my model on FactInternetSales – I have defined a new column with the following formula:

FocusDate = if(

                   selectedvalue(DateFocus[ID]) = 1,

                   FactInternetSales[OrderDate],

                   if (

                          selectedvalue(DateFocus[ID]) = 2,

                          FactInternetSales[DueDate],

                          FactInternetSales[ShipDate]

                      )

              )      

The formula does populate the field initially – with ship date in this case.

The axis of the chart in the UI is FocusDate.  So the chart is interfacing with that column.

 

Here is the problem.  When I change the value of the slicer – I expect contents of the calculated column in the data model to change – thus causing the graph to change slightly.  That is not happening.  Every time I go into the data model – the value of the FocusDate field is ship date.

 

What am I doing worong????

 

Highlighted
New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Slice Facts based on UI events

Calculated columns are populated once, at the time of data refresh.  You want to take the exact same code and make it into a Measure, which are not set at data refresh and change dynamically.

 

Hope this helps

David

Frequent Visitor
Posts: 8
Registered: ‎01-11-2016

Re: Slice Facts based on UI events

Hey David: 

 

I did exactly as you suggested.  I deleted the column in the data model and attempted to add it as a measure on the FactInternetSales table by cut/pasting the code. 

 

That is giving me an error: A  single value for column 'ShipDate' in table 'FactInternetSales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

I do not wnat to aggrigate anything.  What I'm after is more like a case statement then an aggrigate.  Is there a way I can refer to current row??

 

Thank you for the help. 

New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Slice Facts based on UI events

You can apply MIN or MAX to the dates in the formula code without worry as the context for "Current row" will be created by the visual and the measure will be dynamically calculated.

Frequent Visitor
Posts: 8
Registered: ‎01-11-2016

Re: Slice Facts based on UI events

OK.  I've done that.  The formula for the measure is: 

 

FocusDate = if(
                                 selectedvalue(DateFocus[ID]) = 1, 
                                 min(FactInternetSales[OrderDate]),
                                 if (
                                                  selectedvalue(DateFocus[ID]) = 2,
                                                  min(FactInternetSales[DueDate]), 
                                                  min(FactInternetSales[ShipDate])
                                    )
                        )

the aggrigation error went away.  Indeed the measure was added to the table with no error. 

 

Now - when I took the calculated column off of the datamodel, the graphic errored because a data field on the axis was then invalid.  My column chart now has only one column - the value being all time.  When I drag a real date (say Due Date) to axis, the visual breaks into multiple columns as I expect and want.  I remove the real date from Axis, and it goes back to one coilumn - again as I expect.  I drag the Focus Date measure to Axis and - nothing.  No error message.  It just does not drop.  The boarder of the axis does turn yellow telling me it expects me to drop.  But when I release the mouse button - it goes back to Add data field here.

 

How can I put a measure on the axis of a visual?

 

 

Frequent Visitor
Posts: 8
Registered: ‎01-11-2016

Re: Slice Facts based on UI events

I got a little further.  rather then add a measure to FactInternetSales - I added a column.  Same formula as above - but now I do not need the min functions.  I can place the column on the axis - and my bar chart splits into multiple bars as I expect / want.

 

Now what the problem is - when I change the value in the UI slicer - the chart does not adjust.  Am I doing something wrong with selected value?

New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: Slice Facts based on UI events

Calculated columns are populated and fixed at the time of data refresh. SELECTEDVALUE() won't help you there because of the fact that there will be no data refresh and recalculation of the column when the slicer changes.

 

I apologize for not understanding your use case (changing the axis of a visualization based on a slicer) - after re-reading it, what you are trying to do is not possible as far as I am aware. You could do something with buttons and bookmarks and hiding/showing visualizations, but not dynamically change the axis.

 

I will @ a few of the super users on here to see if they have any ideas

 

@Greg_Deckler@AlB, any ideas? 

Community Support Team
Posts: 2,496
Registered: ‎03-15-2018

Re: Slice Facts based on UI events

Hi @jflanner

It seems you want to make a slicer selection that dynamically updates the x-axis. 

You could refer to this article,

http://radacad.com/dynamic-x-axis-on-charts-power-bi

 

if you have any problem, please share some screenshots or example data.

 

Best Regards

Maggie