Power BI Issue: Calculated Items Misbehaving When Filtering

Question

I'm encountering a perplexing issue in my Power BI report that involves calculated items. Any insight or suggestion is much appreciated.

Link to the PBIX file: https://drive.google.com/file/d/1M_jHZchjlJ_HKTNgoR9eupGFtnyG_Zet/view?usp=drive_link

Data Model:

• FactData
• DimProductHierarchy
• DimVersionName1 (disconnected table)
• DimVersionName2 (disconnected table)

Measures:

• [Volume] = SUM(FactData[Volume]

Table Chart1:
Category column - DimProductHierarchy[Level 1]
Value - "Version 1", which is a calculated item I created in Tabular Editor

Table Chart2:
Category column - DimProductHierarchy[Level 1]
Value - "Version 2", which is a calculated item I created in Tabular Editor

Slicer1
Used column - DimVersionName1[Version Name]
Only applied to - Table Chart 1

Slicer2
Used column - DimVersionName2[Version Name]
Only applied to - Table Chart 2

Calculated Item 1 (Version 1):

VAR SelectedVersion1 = SELECTEDVALUE(DimVersionName1[Version Name])
VAR MyFilter = FILTER(ALL(FactData[Version Name]), FactData[Version Name] = SelectedVersion1)
VAR Result = CALCULATE(SELECTEDMEASURE(), MyFilter)
RETURN
    Result

Calculated Item 2 (Version 2):

VAR SelectedVersion2 = SELECTEDVALUE(DimVersionName2[Version Name])
VAR MyFilter = FILTER(ALL(FactData[Version Name]), FactData[Version Name] = SelectedVersion2)
VAR Result = CALCULATE(SELECTEDMEASURE(), MyFilter)
RETURN
    Result

Other Details:

• FactData and DimProductHierarchy are connected with the Many-to-One relationship.

The Problem:
When I click on a category in Table Chart 1, Table Chart 2 turns blank, whereas I need Table Chart 2 to show just that selected category.

Thanks for any suggestions!

Answer 1

In your sample PBIX, when selecting a row in Chart 1, Chart 2 displays nothing - not the same value as Chart 1 as per your problem statement above. So I cannot give you explanation on that. (Feel free to update the given pbix replicating this behaviour and I'll happily take a look.)

For context, you have visual filters applied and so when selecting a row, that context is also applied to other visuals. Since Visual 1 has "Name is Version1" and Visual 2 has "Name is Version2", you will see nothing in Visual 2 when selecting a row in Visual 1.

Suggested solution to give multiple options
Create a new Calculated Item to your Calculation Group with the following:

Version = 
  var v1 = CALCULATE(
    SELECTEDMEASURE(),
    FILTER(
      ALL(FactData[Version Name]),
      FactData[Version Name] = SELECTEDVALUE(DimVersionName1[Version Name])
    )
  )
  var v2 = CALCULATE(
    SELECTEDMEASURE(),
    FILTER(
      ALL(FactData[Version Name]),
      FactData[Version Name] = SELECTEDVALUE(DimVersionName2[Version Name])
    )
  )
  RETURN SWITCH(TRUE(),
    ISFILTERED(DimVersionName1[Version Name]) && ISFILTERED(DimVersionName2[Version Name]), v1 - v2,
    ISFILTERED(DimVersionName1[Version Name]), v1,
    ISFILTERED(DimVersionName2[Version Name]), v2,
    SELECTEDMEASURE()
  )

Then update the visual filters for the three Matrix visuals to "Name is Version". Then ensure Slicer 1 is only applied to Matrix 1 and Matrix 3, Slicer 2 is only applied to Matrix 2 and Matrix 3 - with Matrix 3 being your diff.

Once more, if you did want to show these in one visual, then you can add 'MyCG'[Name] as the Column to your Matrix, and have both slicers applied to the same Matrix. This would give you something like (perhaps rename Version to Version Difference):