Search code examples
powerbidax

Filter multiple visualizations to exclude values that have already been excluded

I'm trying to create 6 unique visuals on Power Bi page, where I'm able to do independent filtering. Franky speaking, when someting is already sliced /filtered on visual 1, should be excluded on visual 2, visual 3 should show values exluded on vis 1 and vis2, visual 4 should show values exluded within vis1 & vis2 & vis3 and so on till vis 6.

enter image description here

Link to pbix file here - > https://drive.google.com/file/d/1Ec5Xv1xubQV_9Fsio05aPbAh12B7RbmE/view?usp=sharing

Any help appreciate.

BR

Solution

  • You will need a Table per visual set. Create the following Calculated Tables. (No relationship needed to these disconnected tables.)

    AccUnits 01 = DISTINCT(Table1[ACC_UNIT])

AccUnits 02 = 'AccUnits 01'

AccUnits 03 = 'AccUnits 01'

AccUnits 04 = 'AccUnits 01'

AccUnits 05 = 'AccUnits 01'

AccUnits 06 = 'AccUnits 01'

    Then create the following Measures:

    Qty 01 = 
  var v = DISTINCT('AccUnits 01'[ACC_UNIT])
  return CALCULATE( SUM(Table1[Qty]), KEEPFILTERS(Table1[ACC_UNIT] IN v ) )


Qty 02 = 
  var v = EXCEPT(
    DISTINCT('AccUnits 02'[ACC_UNIT]),
    DISTINCT('AccUnits 01'[ACC_UNIT])
  )
  return CALCULATE( SUM(Table1[Qty]), KEEPFILTERS(Table1[ACC_UNIT] IN v ) )


Qty 03 = 
  var v = EXCEPT(
    DISTINCT('AccUnits 03'[ACC_UNIT]),
    UNION( 
      DISTINCT('AccUnits 01'[ACC_UNIT]),
      DISTINCT('AccUnits 02'[ACC_UNIT]) 
    )
  )
  return CALCULATE( SUM(Table1[Qty]), KEEPFILTERS(Table1[ACC_UNIT] IN v ) )


Qty 04 = 
  var v = EXCEPT(
    DISTINCT('AccUnits 04'[ACC_UNIT]),
    UNION( 
      DISTINCT('AccUnits 01'[ACC_UNIT]),
      DISTINCT('AccUnits 02'[ACC_UNIT]),
      DISTINCT('AccUnits 03'[ACC_UNIT])  
    )
  )
  return CALCULATE( SUM(Table1[Qty]), KEEPFILTERS(Table1[ACC_UNIT] IN v ) )


Qty 05 = 
  var v = EXCEPT(
    DISTINCT('AccUnits 05'[ACC_UNIT]),
    UNION( 
      DISTINCT('AccUnits 01'[ACC_UNIT]),
      DISTINCT('AccUnits 02'[ACC_UNIT]),
      DISTINCT('AccUnits 03'[ACC_UNIT]),
      DISTINCT('AccUnits 04'[ACC_UNIT]) 
    )
  )
  return CALCULATE( SUM(Table1[Qty]), KEEPFILTERS(Table1[ACC_UNIT] IN v ) )


Qty 06 = 
  var v = EXCEPT(
    DISTINCT('AccUnits 06'[ACC_UNIT]),
    UNION( 
      DISTINCT('AccUnits 01'[ACC_UNIT]),
      DISTINCT('AccUnits 02'[ACC_UNIT]),
      DISTINCT('AccUnits 03'[ACC_UNIT]),
      DISTINCT('AccUnits 04'[ACC_UNIT]),
      DISTINCT('AccUnits 05'[ACC_UNIT])  
    )
  )
  return CALCULATE( SUM(Table1[Qty]), KEEPFILTERS(Table1[ACC_UNIT] IN v ) )

    For Set 1:
    Slicer: Use 'AccUnits 01'[ACC_UNIT]
    Table: Use Table1[ACC_UNIT] and [Qty 01]

    For Set 2:
    Slicer: Use 'AccUnits 02'[ACC_UNIT]
    Table: Use Table1[ACC_UNIT] and [Qty 02]

    And so on for Set 3, 4, 5, & 6.

    Then:
    For Slicer 2, add [Qty 02] as Visual Filter set to is not blank.
    For Slicer 3, add [Qty 03] as Visual Filter set to is not blank.
    For Slicer 4, add [Qty 04] as Visual Filter set to is not blank.
    For Slicer 5, add [Qty 05] as Visual Filter set to is not blank.
    For Slicer 6, add [Qty 06] as Visual Filter set to is not blank.

    And that should do it. Note that each set (table and slicer) will be blank/empty until a selection is made in the proceeding set. You may wish to add a Clear all slicers button, via Insert tab > Buttons > Clear all slicers.