Double Filter on the Same Facts

With edit interactions you can play with selections going into one visual and other selections going into another viual, but you can't combine them afterwards (or I havn't found hwo to).

So I went back to the database. My selections are based on 5 fiels, coming from 3 tables.

So first I created a selections table like this

  CREATE TABLE [edw_aps_goalpubdb].[ENGINE_SELECTIONS_New]
         WITH ( DISTRIBUTION = HASH([ID_PLANNING]),
                CLUSTERED COLUMNSTORE INDEX )
  AS

  SELECT ROW_NUMBER() OVER (ORDER BY [ID_PLANNING]) AS ESEL_UID
       , [ID_PLANNING]
       , [DESCRIPTION]
       , [CONTEXT]
       , [PERIOD_CODE]
	     , [MATERIAL_GROUP_CODE]
	     , [DW_DAY_START]
       , BINARY_CHECKSUM([ID_PLANNING],[CONTEXT],[PERIOD_CODE],[MATERIAL_GROUP_CODE],[DW_DAY_START]) AS ESEL_CHECKSUM
    FROM (SELECT DISTINCT
                 P.[ID_PLANNING]
               , P.[DESCRIPTION]
               , P.[CONTEXT]
               , P.[PERIOD_CODE]
	             , EM.[MATERIAL_GROUP_CODE]
	             , DATEPART(DW,ES.[DAY_START]) AS DW_DAY_START
	          FROM [edw_aps_goalpubdb].[PLANNINGS] P
	               INNER JOIN [edw_aps_goalpubdb].[ENGINE_MATERIAL] EM ON EM.ID_PLANNING = P.ID_PLANNING
                 INNER JOIN [ods_aps_goalpubdb].[ENGINE_SOLUTION] ES ON  ES.ID_PLANNING = EM.ID_PLANNING
                                                                     AND ES.ENGINE_CODE = EM.ENGINE_CODE
         ) X1
  ;

The Facts have one link to this table based on ESEL_UID (Facts are coming from ENGINE_SOLUTION).

Then I created a Cross-Table for all possible selections.

  SELECT ROW_NUMBER() OVER (ORDER BY ESEL_UID, ESEL_VERSION) AS ESEL_2_UID
       , ESEL_VERSION
       , ESEL_UID
       , ID_PLANNING
       , DESCRIPTION
       , CONTEXT
       , PERIOD_CODE
	     , MATERIAL_GROUP_CODE
	     , DW_DAY_START
       , ID_PLANNING_2
       , DESCRIPTION_2
       , CONTEXT_2
       , PERIOD_CODE_2
	     , MATERIAL_GROUP_CODE_2
	     , DW_DAY_START_2
    FROM (SELECT CAST (1 AS SMALLINT)         AS ESEL_VERSION
               , ESEL1.[ESEL_UID]             AS ESEL_UID
               , ESEL1.[ID_PLANNING]          AS ID_PLANNING
               , ESEL1.[DESCRIPTION]          AS DESCRIPTION
               , ESEL1.[CONTEXT]              AS CONTEXT
               , ESEL1.[PERIOD_CODE]          AS PERIOD_CODE
	             , ESEL1.[MATERIAL_GROUP_CODE]  AS MATERIAL_GROUP_CODE
	             , ESEL1.[DW_DAY_START]         AS DW_DAY_START
               , ESEL2.[ID_PLANNING]          AS ID_PLANNING_2
               , ESEL2.[DESCRIPTION]          AS DESCRIPTION_2
               , ESEL2.[CONTEXT]              AS CONTEXT_2
               , ESEL2.[PERIOD_CODE]          AS PERIOD_CODE_2
	             , ESEL2.[MATERIAL_GROUP_CODE]  AS MATERIAL_GROUP_CODE_2
	             , ESEL2.[DW_DAY_START]         AS DW_DAY_START_2
            FROM [edw_aps_goalpubdb].[ENGINE_SELECTIONS] ESEL1
                 CROSS JOIN [edw_aps_goalpubdb].[ENGINE_SELECTIONS] ESEL2
           WHERE ESEL1.ESEL_CHECKSUM <> ESEL2.ESEL_CHECKSUM
          UNION ALL
          SELECT CAST (2 AS SMALLINT)         AS ESEL_VERSION
               , ESEL2.[ESEL_UID]             AS ESEL_UID
               , ESEL1.[ID_PLANNING]          AS ID_PLANNING
               , ESEL1.[DESCRIPTION]          AS DESCRIPTION
               , ESEL1.[CONTEXT]              AS CONTEXT
               , ESEL1.[PERIOD_CODE]          AS PERIOD_CODE
	             , ESEL1.[MATERIAL_GROUP_CODE]  AS MATERIAL_GROUP_CODE
	             , ESEL1.[DW_DAY_START]         AS DW_DAY_START
               , ESEL2.[ID_PLANNING]          AS ID_PLANNING_2
               , ESEL2.[DESCRIPTION]          AS DESCRIPTION_2
               , ESEL2.[CONTEXT]              AS CONTEXT_2
               , ESEL2.[PERIOD_CODE]          AS PERIOD_CODE_2
	             , ESEL2.[MATERIAL_GROUP_CODE]  AS MATERIAL_GROUP_CODE_2
	             , ESEL2.[DW_DAY_START]         AS DW_DAY_START_2
            FROM [edw_aps_goalpubdb].[ENGINE_SELECTIONS] ESEL1
                 CROSS JOIN [edw_aps_goalpubdb].[ENGINE_SELECTIONS] ESEL2
           WHERE ESEL1.ESEL_CHECKSUM <> ESEL2.ESEL_CHECKSUM
         ) X1
  ;

This table cointains quite some recors (in our case 800k) but that is not a problem to tabular.

In PowerBI, i've created these relations, finally you can hide everything in the engine_selections table.

Now my 'left' selections are working on the normal fields like CONTEXT, ID_PLANNING, ... the 'right' selections are working on CONTEXT_2, ID_PLANNING_2, ...

If I want a Visual only for the left selections I put ESEL_VERSION = 1 in the visual filter, same for right (=2)

If I want to see both i just place ESEL_VERSION into rows/columns/legend ....

Works fine.

Maybe there are far more nicer solutions but i couldn't find articles on it and his one works fine. Our fact table holds over 500M records and still satisfied with performance.

Hope this helps you on the way.