Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 ShipDa...
Need Help
dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
7 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      dedelman_clng
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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