Forum Discussion

Mikolajszewczyk's avatar
Mikolajszewczyk
Regular Visitor
7 months ago
Solved

Filtering chart by chart and by slicer at the same time - issue

Hi, recently at my work I was working on a pretty specific report and came across an interesting issue connected to filtering a chart with another chart and a slicer at the same time. I've created a dummy report to visualize and describe my issue.

The report consists of 3 elements:

  1. Decomposition tree – it is explained by 5 columns which contain different statuses. The idea is to visualize different paths that can be taken and show how many records fall into these paths in percent of the whole.

  2. Slicer – a basic slicer with 5 different values.

  3. Another decomposition tree – it is built on the same measure as the first one, but its purpose is to explain the first decomposition tree by characteristics in the slicer. The idea is pretty simple – if we see that 0.09% of our records go into the path (event1 → event1 → event1 → event4 → event3), we look at another decomposition tree to see how that percentage looks across characteristics. The first decomposition tree acts as a filter to the second tree. The chosen path and its percentage should be exactly equal to the percentage on the second tree.

  4. Measures used in the report – the cnt_percent measure is the 'value' of both of these trees.

     

  5. Issue – The issue happens when I try to look at some path and use a slicer at the same time. When I choose the path (event1 → event1 → event1 → event1 → event1), both decomposition trees show different values even though they use the same measure and are filtered by the same values.

    I've checked that this happens when such a path does not exist for characteristics = characteristics5. So what is exactly happening in this situation? I've read that this is because Power BI filtering by chart works differently than standard page filtering, and when I choose a specific path on the first chart it excludes all records that do not fit into that definition even though my measure tells it not to be affected by it.

  6. Tested solutions –

    • Highlighting chart instead of filtering – We can change interactions between charts from filter to highlight. It fixes the issue with wrong calculation, but it is still not a perfect solution because I want my values in the tooltip to be filtered on the second decomposition tree. This solution is also not appealing – all values are displayed even when there is no value to display.

    • Aggregation – Instead of putting my whole dataset into Power BI, I could create the following aggregation logic:

      for every event sequence and all characteristics, create a dummy record showing that the sum of these records is equal to 0. Then Power BI wouldn't be able to filter out that data because a record with such sequence and characteristics exists and is equal to 0. The main issue with this solution is that it can only work on smaller datasets. Creating aggregations for +1 million records and over 20 characteristics, as in my original report, would result in calculation of over a billion records.

I’m looking for any fix / workaround for this issue, both from the perspective of changing how I insert data into the report or how I define measures and charts. Also worth noting is that i use version of PBI from around the beginning of 2025, so if any of the proposed fixes contains some function from newest wersion it might not fix the issue for me. 

 

Need Help

6 Replies

  • DanieleUgoCopp's avatar
    DanieleUgoCopp
    Icon for Super User rankSuper User
    7 months ago

    Hello,

    I think that the simplest way to fix this is to stop letting the first decomposition tree filter the second one directly. Instead create a small disconnected table that only stores the selected path values and use a measure with TREATAS to apply that selection logically,
    this way the slicer and the path selection are combined inside the measure, not by the visual engine, Power BI will then calculate percentages correctly even when some path and characteristic combinations do not physically exist.

    • Mikolajszewczyk's avatar
      Mikolajszewczyk
      Regular Visitor
      7 months ago

      Hey Daniele

      Could you elaborate how such disconnected table should look like and how to use treatas in this context? I've never used treatas function and have no idea how to put your suggestion into reality

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    7 months ago

    Hi Mikolajszewczyk ,

     

    Can yoy please share the sample data. in my view, the calculation is getting wrong based on how the percentage calculated and how the characteristic is mapped in the table for each events.

    • Mikolajszewczyk's avatar
      Mikolajszewczyk
      Regular Visitor
      7 months ago

      Hi rupak,

      I dont see an option to send data here, so i think the easiest way would be to give you code to create it by yourself. I have used a excel makro to create it. Here is a sample code:

      Sub Generate60000()

    Const ROWS_COUNT As Long = 60000
    Const STATUS_COLS As Long = 5
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)
    ws.Cells.Clear
    
    ws.Range("A1:G1").Value = Array( _
        "customer_number", _
        "status1", "status2", "status3", "status4", "status5", _
        "characteristic")
    
    Dim data()
    ReDim data(1 To ROWS_COUNT, 1 To 7)
    
    Dim i As Long, c As Long
    Dim allEvent1 As Boolean
    Dim ch As String
    
    Randomize
    
    For i = 1 To ROWS_COUNT
        
        data(i, 1) = i   ' customer_number
        allEvent1 = True
        
        ' Statusy
        For c = 1 To STATUS_COLS
            data(i, 1 + c) = "event" & Int(Rnd * 4) + 1
            If data(i, 1 + c) <> "event1" Then allEvent1 = False
        Next c
        
        If allEvent1 Then
            ch = "characteristic" & Int(Rnd * 4) + 1   ' 1–4
        Else
            ch = "characteristic" & Int(Rnd * 5) + 1   ' 1–5
        End If
        
        data(i, 7) = ch
        
    Next i
    
    ws.Range("A2").Resize(ROWS_COUNT, 7).Value = data

      It creates 60k of records, and also makes sure that one path - event (1-1-1-1-1) doesnt not have a representative in the characteristics5 

    • Mikolajszewczyk's avatar
      Mikolajszewczyk
      Regular Visitor
      7 months ago

      Hi sergi, I've check your file and indeed it fixes my issue. I am really suprised that solution even exists because i've been searching for some fix for a very long time. I am very grateful for the time you devoted and your great knowledge. I have marked your solution as the answer, but I have one more question. How would you solve this problem for many characteristic columns? Is there a 'smart way' to do it, or do I have to create such a side table for each one?

      My original raport have around 20+ columns that i want to use as filters, so i wonder what is the most efficient way to implement this