Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ?
Solved! Go to Solution.
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.
@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
OK, here is the data for the 'left' filter
KeyCompVersionSelKeyDim1Dim2Dim3Dim4Dim5
1.1 | 1 | 1 | Plan1 | XX | null | OK | 01/02/2018 |
1.2 | 1 | 2 | Plan1 | XX | null | OK | 02/02/2018 |
1.3 | 1 | 3 | Plan1 | null | X123 | null | 01/02/2018 |
1.4 | 1 | 4 | Plan1 | XY | null | NOK | 01/02/2018 |
1.5 | 1 | 5 | Plan1 | XY | null | NOK | 02/02/2018 |
1.6 | 1 | 6 | Plan2 | ZZ | null | null | 01/03/2018 |
1.7 | 1 | 7 | Plan2 | ZZ | null | null | 02/03/2018 |
1.8 | 1 | 8 | Plan3 | ZZ | null | null | 01/01/2017 |
1.9 | 1 | 9 | Plan4 | null | null | null | 01/01/2018 |
The right Dim/Selector
KeyCompVersionSelKeyDim1Dim2Dim3Dim4Dim5
2.1 | 2 | 1 | Plan1 | XX | null | OK | 01/02/2018 |
2.2 | 2 | 2 | Plan1 | XX | null | OK | 02/02/2018 |
2.3 | 2 | 3 | Plan1 | null | X123 | null | 01/02/2018 |
2.4 | 2 | 4 | Plan1 | XY | null | NOK | 01/02/2018 |
2.5 | 2 | 5 | Plan1 | XY | null | NOK | 02/02/2018 |
2.6 | 2 | 6 | Plan2 | ZZ | null | null | 01/03/2018 |
2.7 | 2 | 7 | Plan2 | ZZ | null | null | 02/03/2018 |
2.8 | 2 | 8 | Plan3 | ZZ | null | null | 01/01/2017 |
2.9 | 2 | 9 | Plan4 | null | null | null | 01/01/2018 |
Facts Data
SelKeyKMPrice
1 | 15 | 150 |
1 | 16 | 149 |
1 | 17 | 148 |
1 | 18 | 147 |
2 | 19 | 146 |
2 | 20 | 145 |
2 | 21 | 144 |
2 | 22 | 143 |
3 | 23 | 142 |
3 | 24 | 141 |
3 | 25 | 140 |
3 | 26 | 139 |
4 | 27 | 138 |
4 | 28 | 137 |
4 | 29 | 136 |
4 | 30 | 135 |
5 | 31 | 134 |
5 | 32 | 133 |
5 | 33 | 132 |
5 | 34 | 131 |
6 | 35 | 130 |
6 | 36 | 129 |
6 | 37 | 128 |
6 | 38 | 127 |
7 | 39 | 126 |
7 | 40 | 125 |
7 | 41 | 124 |
7 | 42 | 123 |
8 | 43 | 122 |
8 | 44 | 121 |
8 | 45 | 120 |
8 | 46 | 119 |
9 | 47 | 118 |
9 | 48 | 117 |
9 | 49 | 116 |
9 | 50 | 115 |
So the idea is that we woudl ahev something like this
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.
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.
Let me know if you can help!
Thanks!!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |