Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Double Filter on the Same Facts

Hello I have a 'dim-selection ' table, like

 

SelKey, Dilm1-Field1, Dim1-Field2,Dim2-Field1,Dim2-Field3

 

The file is created from other dimensions and has 1 key, that key goed back to the Facts.

 

The goadl would be that on the left off the csreen the used choses one set of selections and sees the results in a visual. On the right a second selection also to a visual.

This works fine when using 'Edit Interactions'

But now, the user would like to have a chart with both values compared ...

 

As the Facts holds over 500miljon rows, and there are over 90 measures defined I would like to find a structural solution instead of doubling all measures and use a USERELATIONSHIP on the doubled versions ...

 

Any idea if this would be possible ?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

2018-05-30 22_43_08-Test2Dim_v2 - Power BI Desktop.png

 

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.

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@Anonymous,

Based on the above description, it is difficult for us to provide you solution. Could you please share dummy data of your table and post expected result here?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

OK, here is the data for the 'left' filter

KeyCompVersionSelKeyDim1Dim2Dim3Dim4Dim5

1.111Plan1XXnullOK01/02/2018
1.212Plan1XXnullOK02/02/2018
1.313Plan1nullX123null01/02/2018
1.414Plan1XYnullNOK01/02/2018
1.515Plan1XYnullNOK02/02/2018
1.616Plan2ZZnullnull01/03/2018
1.717Plan2ZZnullnull02/03/2018
1.818Plan3ZZnullnull01/01/2017
1.919Plan4nullnullnull01/01/2018

 

The right Dim/Selector

KeyCompVersionSelKeyDim1Dim2Dim3Dim4Dim5

2.121Plan1XXnullOK01/02/2018
2.222Plan1XXnullOK02/02/2018
2.323Plan1nullX123null01/02/2018
2.424Plan1XYnullNOK01/02/2018
2.525Plan1XYnullNOK02/02/2018
2.626Plan2ZZnullnull01/03/2018
2.727Plan2ZZnullnull02/03/2018
2.828Plan3ZZnullnull01/01/2017
2.929Plan4nullnullnull01/01/2018

Facts Data

SelKeyKMPrice

115150
116149
117148
118147
219146
220145
221144
222143
323142
324141
325140
326139
427138
428137
429136
430135
531134
532133
533132
534131
635130
636129
637128
638127
739126
740125
741124
742123
843122
844121
845120
846119
947118
948117
949116
950115

 

So the idea is that we woudl ahev something like this

 

2018-05-30 09_21_37-Test2Dim - Power BI Desktop.png

 

The 1 is the left (Dim1) and shows wich Selkey records should be selected.

The 2 is the right (Dim2) and shows wich Selkey for the second selection.

Dim1 and Dim2 are completely identical except then CompVersion 1 or 2.

Finaly in the matrix I would like to see only the values for the selected SelKeys, By Compversion ...

 

Hope this is a bit more clear.

Anonymous
Not applicable

I did find a solution, without specials tricks, just a design with related tables.

If anybody would need this, just PM me.

 

@Anonymous

 

I'm trying to do a similar thing, but couldn't find a solution.

I'm comparing two events: I use "edit interactions" to get the metrics for each event, but I also want a table showing just people who went to both events. Right now, the table doesn't show any names.

image.png

 

 

 

 

 

 

 

 

 

 

Let me know if you can help!

Thanks!!

 

Anonymous
Not applicable

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.

 

2018-05-30 22_43_08-Test2Dim_v2 - Power BI Desktop.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.