Forum Discussion
Continued ContributorManaged to Isolate Strange MMR Error - PBIX Link
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
_IsXAxisThis 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:
This is my first time to post a PBIX, so if the link is not working, please let me know.
Many thanks in advance!
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!
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
3 Replies
WishAskedSoonerI 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
Super User
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!