Search code examples
daxpowerbi-desktop

Discconnected tables - filter

I have a matrix table which calculates dept cost for selected projects. To acheive this, I created two disconnected tables. I want to filter disconnected tables based on the country column (facts table) i.e if a country is selected, the disconnected tables which are used as slicers return the projects available in the selected country.

What I have done - I created two disconnected tables(Slicer_1 & Slicer_2).

**Slicer_1 = VALUES(Facts_table[project]) **

**Slicer_2 = VALUES(Facts_table[project]) **

I created the measures below for the selected projects.

cost_project1 = CALCULATE([cost_measure], FILTER(dim,dim[project] in {SELECTEDVALUE(Slicer_1[project])}))

cost_project2 = CALCULATE([cost_measure], FILTER(dim,dim[project] in {SELECTEDVALUE(Slicer_2[project])}))

What I am expecting - I want to add the country slicer so when a country is selected, Slicer_1 & Slicer_2 returns the projects in selected country. The aim is to enable me compare costs between projects in the same location. The challenge I have is that the country slicer has no interaction with the disconnected table slicers.

Please how do I acheive this?

Solution

  • There are a couple of options.

    1. Use a measure per slicer as a Visual Filter

    Create two new measures:

    Slicer_1 count = CALCULATE(
    COUNTROWS('Facts_table'),
    'Facts_table'[project] = SELECTEDVALUE('Slicer_1'[project])
  )

Slicer_2 count = CALCULATE(
    COUNTROWS('Facts_table'),
    'Facts_table'[project] = SELECTEDVALUE('Slicer_2'[project])
  )

    Then add each measure to their respective slicer as a Visual Filter, to either is not blank or is greater than 0.


    2. Include Country in your slicer tables and add Country relationship

    Update your slicers disconnected tables to:

    Slicer_1 = SUMMARIZECOLUMNS('Facts_table'[project], 'Facts_table'[Country])

    Then add relationship to your Country table.