cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HL_YPTO Member
Member

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

Accepted Solutions
Highlighted
HL_YPTO Member
Member

Re: 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.

 

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
Moderator v-yuezhe-msft
Moderator

Re: Double Filter on the Same Facts

@HL_YPTO,

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.
HL_YPTO Member
Member

Re: Double Filter on the Same Facts

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.

HL_YPTO Member
Member

Re: Double Filter on the Same Facts

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

If anybody would need this, just PM me.

 

lzimon Frequent Visitor
Frequent Visitor

Re: Double Filter on the Same Facts

@HL_YPTO

 

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!!

 

Highlighted
HL_YPTO Member
Member

Re: 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.

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 408 members 3,351 guests
Please welcome our newest community members: