Benchmarks table in Star Schema model

[Julien_F]

Hello, 

I am stuck with an issue, I mean not an issue but not enouth skills from my side to solve that one. 

I checked the forum and found some path to solve it but I only solved a part of it 😕

Here we go: 

- I have a star schema model ( look the picture ) with several dimensions and one fact table. All dimension attribute are not in the fact table, for example language is in the dimension level 3 and not in the fact with a bolean value ( only two language ). Maybe that detail is important for my issue, I don't know. 

- The star schema is working well as I am able to dashboard everything. 

Client asked me to add a benchmark table... 

Benchmarck is base on metrics from the fact and some dimension attributes, let 's say one or two attributes by dimension such as : 

Level one: Campaign name and cluster 

Level two: Strategy and publisher 

Level three: target and format 

The idea is if I pick a campaign I will be able in the same table to see its becnhmarks because the campaign I want to analyze has the same attirbute ( except the name of course) but same publisher, strategy, target and format. 

Also I would like to add seasonality : if the campaign I choose run between 01/01/2020 and 15/01/20 it should give the bench for the attributes I was talking above and the mont JAN as I will group my bench by 6 or 7 dim atribute and month_year. 

Then the Rolls Royce could be adding some conditional fromating to say if above bench than GREEN if abose RED, 

I have part of the solution by using : 

Calculate, summerize, addcolums but sometine it deosn't work or I have to do it with several filters, 

--> I am stuck 

Thx for your help, 

J.

[Julien_F]

Hi, 

To give extra info, 

I tried with a measure  :

CALCULATE (

[CPM],

SUMMARIZE (level3_dimension,level3_dimension[X],level3_dimension[Y],level3_dimension[Z]),

ALL(level1_dimension[A])

)

+: It is in the fact

-:  - only one metric but I could create more than once so it is not a big issue 

   - If I filter the table benchmark is no more accurate , except for campaign because of the (ALL)

   - the ALL function does not accept attributes from other table like once in DIM 2 and 3 

Then I tried with a table : 

Table =

var combinetable = ADDCOLUMNS('fact',"test",RELATED(level2_dimension[X]),"test2",RELATED(level3_dimension[Y]),"test4",RELATED(level3_dimension[Z]),"test5",RELATED(date_dimension[date_date].[Month]),"test6",related(level2_dimension[w]),"test7",RELATED(level2_dimension[s]))

return

SUMMARIZE(combinetable,[test],[test2],[test4],[test5],[test6],[test7],digital_dashboard_facts[click],digital_dashboard_facts[cost],digital_dashboard_facts[impression])

+: I have everything

- : cannot make a relation with my star schema

It is possible to filter that tabe based on another table? Like a simple table showing: atribute like test, test2... and filter that table based on the table value? 

Picture below to explain better whet I want as benchmark in my "campaign" results visualisation tool and where is it from:

Thx you,

J.