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.
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
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |