Slice Facts based on UI events

Question

Folks:I have a problem that I have recreated in the attached .pbix file.&nbsp; This file contains only AdventureWorks data.&nbsp;AdventureWorks data can be looked at based on OrderDate, DueDate or ShipDate.&nbsp; (Only one of these dates can be related to DimDate.&nbsp; In my model it is OrderDate.&nbsp; The other two relationships are inactive.)&nbsp;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.&nbsp; The slicer works.&nbsp; The table view above the slicer will change as you click on various buttons.&nbsp;So – in my model on FactInternetSales – I have defined a new column with the following formula:FocusDate = if(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; selectedvalue(DateFocus[ID]) = 1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FactInternetSales[OrderDate],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; selectedvalue(DateFocus[ID]) = 2,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FactInternetSales[DueDate],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FactInternetSales[ShipDate]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The formula does populate the field initially – with ship date in this case.The axis of the chart in the UI is FocusDate.&nbsp; So the chart is interfacing with that column.&nbsp;Here is the problem.&nbsp; 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.&nbsp; That is not happening.&nbsp; Every time I go into the data model – the value of the FocusDate field is ship date.&nbsp;What am I doing worong????&nbsp;

dedelman_clng · Answer

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

anonymous · Answer

Hey David:&nbsp;&nbsp;I did exactly as you suggested.&nbsp; 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.&nbsp;&nbsp;That is giving me an error: A&nbsp;&nbsp;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.&nbsp;I do not wnat to aggrigate anything.&nbsp; What I'm after is more like a case statement then an aggrigate.&nbsp; Is there a way I can refer to current row??&nbsp;Thank you for the help.&nbsp;

dedelman_clng · Answer

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

anonymous · Answer

OK.&nbsp; I've done that.&nbsp; The formula for the measure is:&nbsp;&nbsp;FocusDate = if(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;selectedvalue(DateFocus[ID]) = 1,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;min(FactInternetSales[OrderDate]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; selectedvalue(DateFocus[ID]) = 2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; min(FactInternetSales[DueDate]),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; min(FactInternetSales[ShipDate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )the aggrigation error went away.&nbsp; Indeed the measure was added to the table with no error.&nbsp;&nbsp;Now - when I took the calculated column off of the datamodel, the graphic errored because a data field on the axis was then invalid.&nbsp; My column chart now has only one column - the value being all time.&nbsp; When I drag a real date (say Due Date) to axis, the visual breaks into multiple columns as I expect and want.&nbsp; I remove the real date from Axis, and it goes back to one coilumn - again as I expect.&nbsp; I drag the Focus Date measure to Axis and - nothing.&nbsp; No error message.&nbsp; It just does not drop.&nbsp; The boarder of the axis does turn yellow telling me it expects me to drop.&nbsp; But when I release the mouse button - it goes back to Add data field here.&nbsp;How can I put a measure on the axis of a visual?&nbsp;&nbsp;

anonymous · Answer

I got a little further.&nbsp; rather then add a measure to FactInternetSales - I added a column.&nbsp; Same formula as above - but now I do not need the min functions.&nbsp; I can place the column on the axis - and my bar chart splits into multiple bars as I expect / want.&nbsp;Now what the problem is - when I change the value in the UI slicer - the chart does not adjust.&nbsp; Am I doing something wrong with selected value?

Forum Discussion

Slice Facts based on UI events

7 Replies

Related content

MQTT Events debugging

Storage trigger event

How to drop duplicates in event stream using event processor ?

Events Dashboard

Relationship with Fact tables