Managed to Isolate Strange MMR Error - PBIX Link

[WishAskedSooner]

Hi Experts!

After extensive debugging I have managed to isolate a strange error related to a MMR between two fact tables. However, I am stumped as to why this is happening although I have some clues. Please keep reading.

Simple Synopsis

I have two fact tables joined with a MMR. One table is used to flip between two different X-Axis values with a slicer while the other table is used for the Y-Axis values. When filtered properly, the two fact tables are one-to-one.

The Problem

The kick up is not correct. This corresponds to the X-Axis Value of 1.50.

The X-Axis plots Values from the MMR table that are either ATTRIBUTE_IDs 106 and 206, or ATTRIBUTE_IDs 111 and 211 using the following measure:

IsXAxisSel = 
    VAR _SelAxis = SELECTEDVALUE(switchVolXAxis[ID])

    VAR _ECA =
        SWITCH(
            _SelAxis,
            1, 106,
            2, 111
        )

    VAR _EPA =
        SWITCH(
            _SelAxis,
            1, 206,
            2, 211
       )

    VAR _IsXAxis =
        IF(
            MAX(factTableMMR[ATTRIBUTE_ID]) IN {_ECA, _EPA},
            1,
            0
        )

RETURN
    _IsXAxis

This measure is then loaded into the Filters for the Plot and set to: is 1.

What the Plot Should Look Like

I can get the plot to look correct if I hard code the ATTRIBUTE_ID as 106 and 206 in the filter pane instead of using the measure/slicer:

Clues

The Value 1.50 shows up twice in the MMR Table and is the only non-unique Value. However, the ATTRIBUTE_IDs are 111 and 206 and are not plotted at the same time per the above measure. Why it works when I hard code the ATTRIBUTE_ID but not when I use the measure/slicer makes no sense.

PBIX Link

The PBIX is very simple and you can see for yourself with the link below:

Test3.0.pbix

This is my first time to post a PBIX, so if the link is not working, please let me know.

Many thanks in advance!

[WishAskedSooner · Accepted solution]

I realize that this is a hard problem that few DAX experts would have much insight into unless they have a lot of experience working with EAV tables.

One solution I found was to simply pivot the MMR table into the different columns I want to plot as the X-Axis values then put those columns into a field parameter. Plus, it solved my field parameters problem because I couldn't get them to work previously.

For those really wondering, I used PowerQuery to pivot the MMR table into one column for ATTRIBUTE_IDs 106 and 206, and a second column for ATTRIBUTE_IDs 111 and 211. I know the data well and there are never non-unique values in those ATTRIBUTE_ID combinations.

I will mark this post as a solution, but I am still really curious as to why my original solution doesn't work. Did I stumble across some undocumented error/behavior? Please let me know if you discovered the reason!

[WishAskedSooner · Accepted solution]

I realize that this is a hard problem that few DAX experts would have much insight into unless they have a lot of experience working with EAV tables.

One solution I found was to simply pivot the MMR table into the different columns I want to plot as the X-Axis values then put those columns into a field parameter. Plus, it solved my field parameters problem because I couldn't get them to work previously.

For those really wondering, I used PowerQuery to pivot the MMR table into one column for ATTRIBUTE_IDs 106 and 206, and a second column for ATTRIBUTE_IDs 111 and 211. I know the data well and there are never non-unique values in those ATTRIBUTE_ID combinations.

I will mark this post as a solution, but I am still really curious as to why my original solution doesn't work. Did I stumble across some undocumented error/behavior? Please let me know if you discovered the reason!

[OwenAuger · Accepted solution]

Hi @WishAskedSooner 

Glad you have a working solution!

I did just have a look at this, and the explanation for the "kick up" is to do with the visual-level filter using IsXAxisSel:

• In the top chart, IsXAxisSel is evaluated for each value of factTableMMR[VALUE] on the axis of the visual, and data points are included only if IsXAxisSel evaluates to 1 in the context of that axis value.
• The visual-level filter only determines whether that particular VALUE is included, but does not filter ATTRIBUTE_ID.
• With X-Axis A selected on the slicer, IsXAxisSel returns 1 if MAX( factTableMMR[ATTRIBUTE_ID] ) is either 106 or 206.
• For the datapoint with VALUE = 1.50, the two ATTRIBUTE_ID values present in the filter context are 111 and 206, so the maximum is 206, which results in IsXAxisSel = 1.
• If you want to include only ATTRIBUTE_ID values of 106 and 206, this cannot be done with a visual-level filter alone, because the visual-level filter only filters the values on the axis.
• To make your logic work, you would have to either write a measure containing the filtering logic, or carry out some further modelling to translate the Axis selection into a filter on ATTRIBUTE_ID (via some intermediate tables and relationships).
• One possible measure that you could use is:

Y-Axis Value Modified =
VAR _SelAxis = SELECTEDVALUE ( switchVolXAxis[ID] )
VAR _ECA = SWITCH (
        _SelAxis,
        1, 106,
        2, 111
    )
VAR _EPA = SWITCH (
        _SelAxis,
        1, 206,
        2, 211
    )
RETURN
    CALCULATE (
        [Y-Axis Value],
        KEEPFILTERS ( factTableMMR[ATTRIBUTE_ID] IN { _ECA, _EPA } )
    )

If you wanted to verify some of the above points, you could break down the IsAxisSel measure into components and display them in a matrix visual, and also examine the DAX query generated by the line chart using Performance Analyzer.

Regards

[WishAskedSooner]

Hi @OwenAuger

Wow! A simply stunning solution and explanation. Very well written and thought out with an amazing amount of detail. I cannot thank you enough.

I tried your Y-Axis Value Modified measure and it works perfectly! I would've never thought of using KEEPFILTERS like that.

I definitely need to spend some time with the Performance Analyzer and break IsAxisSel into its components.

Thank you!