The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I am struggling for a couple of days with a problem that looked simple but I dont manage to solve. I would appreciate your help.
I have a data set that looks like this:
var1 | var2 | error_x | error_y | n_events |
val1 | val3 | a | a | 1000 |
val2 | val3 | a | a | 135 |
val1 | val3 | a | b | 12 |
val2 | val4 | a | c | 1 |
val2 | val4 | b | a | 39 |
val1 | val5 | b | b | 100 |
val2 | val4 | b | b | 85 |
val1 | val4 | c | c | 2 |
var1 and var2 are not involved in the calculations I need to do, but they are relevant because I would like the calculations to take into account the filters that are applied to var1 and var2. Actually, I have managed to calculate what I need using "columns", but I need to use "measures" instead, so that the results take the filters into account.
These are the columns that I have defined:
error_x | error_y | error_x_agg | error_x_index | error_x_index_norm |
a | a | 1148 | 0.98867596 | 0.91907485 |
a | b | 1148 | 0.01045296 | 0.00628848 |
a | c | 1148 | 0.00087108 | 0.00087032 |
b | a | 224 | 0.17410714 | 0.08092515 |
b | b | 224 | 0.82589286 | 0.99371152 |
c | c | 2 | 1 | 0.99912968 |
What I need is to do this but in such a way that if I filter some values out with var1 and var2, all the calculations are done only on the data that remains. As far as I understand (I am quite new to Power BI) I need to use measures instead of columns to achieve this. For example, if I filter with var1 = val1, I expect:
error_x | error_y | error_x_agg | error_x_index | error_x_index_norm |
a | a | 1012 | 0.98814229 | 1 |
a | b | 1012 | 0.01185771 | 0.01171875 |
b | b | 100 | 1 | 0.98828125 |
c | c | 2 | 1 | 1 |
Solved! Go to Solution.
Hi, @Anonymous ,
not sure what you are trying to do, but here is a possible strategy for you.
First create separate tables for Table[Var1] and Table[Var2], one table for each. Use these two tables for slicers. Then create measures like this:
error_x_agg =
IF (
ISFILTERED ( Table_Var1[var1] );
CALCULATE (
SUM ( 'Table'[n_events] );
FILTER (
ALLEXCEPT ( 'Table'; 'Table'[error_x] );
NOT ( 'Table'[var1] IN VALUES ( Table_Var1[var1] ) )
)
);
CALCULATE ( SUM ( 'Table'[n_events] ); ALLEXCEPT ( 'Table'; 'Table'[error_x] ) )
)
This example only considers var1, you will have to see how you want to handle situations like: filter on Var1 and not Var2 vs filter on Var2 and not on Var1
Cheers,
Sturla
Hi @Anonymous ,
Not completely solved. I fail to make the third measure "error_x_index_norm" does not work. PBIX file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ,
not sure what you are trying to do, but here is a possible strategy for you.
First create separate tables for Table[Var1] and Table[Var2], one table for each. Use these two tables for slicers. Then create measures like this:
error_x_agg =
IF (
ISFILTERED ( Table_Var1[var1] );
CALCULATE (
SUM ( 'Table'[n_events] );
FILTER (
ALLEXCEPT ( 'Table'; 'Table'[error_x] );
NOT ( 'Table'[var1] IN VALUES ( Table_Var1[var1] ) )
)
);
CALCULATE ( SUM ( 'Table'[n_events] ); ALLEXCEPT ( 'Table'; 'Table'[error_x] ) )
)
This example only considers var1, you will have to see how you want to handle situations like: filter on Var1 and not Var2 vs filter on Var2 and not on Var1
Cheers,
Sturla
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
252 | |
121 | |
112 | |
81 | |
70 |