Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a model where I have my fact and Project Dimension:
But I also have a 1:* relationship in Project and Roles.
That is:
Fact * - 1Project
Project1 - * Roles (for a given project I have 3 ppl: ‘responsible’, ‘boss’, ‘control’)
I have this measure that I am using (in a matrix visual), where I display some custom aggregation:
My levels are:
.
Result:= VAR _part1 = SUMX ( SUMMARIZE ( FILTER ( ALL ( 'Fact' ), Fact[Level1] = MAX ( Fact[Level1] ) && Fact[Level2] IN VALUES ( Fact[Level2] ) ), Fact[Level1], Fact[Level2] ), [Basic] ) VAR _part2 = SUMX ( SUMMARIZE ( FILTER ( ALL ( Fact ), Fact[End Customer] = MAX ( Fact[End Customer] ) && Fact[Level1] IN VALUES ( Fact[Level1] ) && Fact[Level2] IN VALUES ( Fact[Level2] ) ), Fact[End Customer], Fact[Level1], Fact[Level2] ), [Basic] ) Return SWITCH ( TRUE(), ISINSCOPE (Fact[Level3] ), [Basic], ISINSCOPE (Fact[Level2] ), [Basic], ISINSCOPE (Fact[Level1] ), _part1, ISINSCOPE (Fact[End Customer] ), _part2, )
Now , to the top level (in my matrix visual) I want to add Roles[Name] ... Of course I need a ISINSCOPE for this case:
ISINSCOPE (Roles[Name] ), xyz,
But I have tried writing the xyz code in every way and I can't make it work
(For all my other cases it works, because I bring the columns level1,level2, etc from the dim to the fact, but now I can’t do this because I have a * to * relationship).
(the agreggation shall be just a SUM of whatever is on End Customer)
Hi @ovonel ,
In order to better understanding your demands and give the right solution, could you please share with me some screenshots of your data after hiding sensitive information and tell me what's your expected output?
Thanks for your efforts & time in advance.
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks @v-yadongf-msft
Yes, I have this visual:
In Green is level3, and brown level2.
This works perfectly fine, with the measure described...
But, now i want to add a higher level: Roles[EmployeeName]
As I mentioned, the model is:
Fact * - 1Project
Project1 - * Roles (for a given project I have 3 ppl: ‘responsible’, ‘boss’, ‘control’)
the roles table has an structure like:
Proj | Roles | Name |
1 | R | John |
1 | B | Tim |
1 | C | Laura |
2 | R | Hector |
2 | B | Thomas |
2 | C | Tim |
My expected output of the matrix would be levels:
I have a model where I have my fact and Project Dimension:
But I also have a 1:* relationship in Project and Roles.
That is:
Fact * - 1Project
Project1 - * Roles (for a given project I have 3 ppl: ‘responsible’, ‘boss’, ‘control’)
I have this measure that I am using (in a matrix visual), where I display some custom aggregation:
My levels are:
.
Result:=
VAR _part1 =
SUMX (
SUMMARIZE (
FILTER (
ALL ( 'Fact' ),
Fact[Level1] = MAX ( Fact[Level1] ) && Fact[Level2] IN VALUES ( Fact[Level2] )
),
Fact[Level1],
Fact[Level2]
),
[Basic]
)
VAR _part2 =
SUMX (
SUMMARIZE (
FILTER (
ALL ( Fact ),
Fact[End Customer] = MAX ( Fact[End Customer] ) && Fact[Level1] IN VALUES ( Fact[Level1] ) && Fact[Level2] IN VALUES ( Fact[Level2] )
),
Fact[End Customer],
Fact[Level1],
Fact[Level2]
),
[Basic]
)
Return
SWITCH (
TRUE(),
ISINSCOPE (Fact[Level3] ), [Basic],
ISINSCOPE (Fact[Level2] ), [Basic],
ISINSCOPE (Fact[Level1] ), _part1,
ISINSCOPE (Fact[End Customer] ), _part2,
)
Now , to the top level (in my matrix visual) I want to add Roles[Name] ; so I need some sort of:
ISINSCOPE (Roles[Name] ), xyz,
I have tried every way and I can't make it work (for all the other cases it works to bring the columns level1,level2, etc from the dim to the fact, but now I can’t do this because I have a * to * relationship).
(the agreggation shall be just a SUM of whatever is on End Customer)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |