Forum Discussion
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.
Community ChampionYou 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.
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?
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?
- dedelman_clng
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?
v-juanli-msftCommunity Support
Hi Anonymous
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