daxpowerbi-desktoptabular-editor

Power BI Issue: Calculated Items Misbehaving When Filtering

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:

Measures:

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:

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!

Solution

  • 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.

    enter image description here

    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):
    enter image description here