Dynamically Changing Measures from Amount to Quantity

[RalphDodson]

I have 2 measures in my fact table. SalesAmount and ShippedQuantity. I would like to place the SalesAmount on a matrix and have the user click a place on the report to change the measure from SalesAmount to ShippedQuantity and back again at will. I want to avoid the need to have 2 identical matrix using different measures. I'm assuming the resolution will involve DAX.

After searching the web and the forums for many hours I still cannot find any reference to this simple feature.

[Anonymous]

@RalphDodson,

Use a disconnected table that contains the measure names you want to make available to the user.  This table will be what you use for the slicer.  Next, create the measures that reference the Disconnected tables and return the users selection.  This is outlined on a couple posts on my blog, bipatterns.com.

Two posts I'd recommend checking out: Dynamic PowerBI Reports Via DAX and Compare two Stores Based on User Selected Measure.  

So if my model looks like this:

First Step:

Create the base Measures and KPI’s needed. In this example, the measures consisted of TNS (Total Net Sales), Returns, Retail Sales, Apparel Sales %, Decor Sales %, Memorabilia Sales %, etc. These are all relatively basic DAX Measures, using nothing more than Calculate, Sum, and Divide.

Step Two:

Create the disconnected tables, highlighted in red above.  These tables will consist of two columns, Position and Variable (Column or Line Variable for example).  The position column serves as a row index, while the Variable column will contain the names of the measures you want to use dynamically.

Step Three:

Create the measures that reference the Disconnected tables.

Line Measures:

Selected Line Measure Position

SelectedLinePosition =
MIN ( Line[Position] )

SelectedLineMeasure =
SWITCH (
    [SelectedLinePosition],
    1, [Apparel Sales %],
    2, [Décor Sales %],
    3, [Memorabilia Sales %],
    4, [Tailgate Sales %],
    5, [Online Sales %],
    6, [Retail Sales %],
    BLANK (), [Apparel Sales %] )

Line Measure Name =
CONCATENATE (
    “Line Measure: “,
    LOOKUPVALUE (
        Line[Line Measure],
        Line[Position], CALCULATE ( MIN ( Line[Position] ) )
    )

   )

Let me know if you have questions on anything, happy to help.

Thanks,

Ryan Durkin

[TAlvarez]

Truly people undestimate how much timeand space  you can save doing dynamic measures and dimensions, Also remember you can format the measure in case the aggregation is not on the same level.

I have been trying to do the same for Dimensions in the last 2 months but I haven't had luck.