Forum Discussion
Calculated field and filtering
If values need to change with slicer selection then you cannot use a calculated column, it has to be a measure.
Few changes are needed here:1) change relationships to what you see below, in visuals and formulas only use relating to tasks use only columns from 'TASK' table as it will propagate filter to TASK_DISTRIBUTION and TASK_VALUES
it removes the bidirectional relationship (which is usually a bad practice, more details here https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/ around 14:30)
2) add this measure (it will work with relationships like above)
Measure = VAR __ValuesWithPercent = ADDCOLUMNS ( 'TASK_VALUES', "%", CALCULATE ( SUM ( 'TASK_DISTRIBUTION'[%F] ) ) ) RETURN SUMX ( __ValuesWithPercent, [Value] * [%] / 100 )
Hello Stachu !
Model looks like this:
TASK_VALUES with TASK_DISTRIBUTION as many-to-may
TASK with TASK_VALUES as one-to-many
FAMILY with TASK_DISTRIBUTION as one-to-many
TASK_DISTRIBUTION
Task Family %F
| TASK1 | FAM1 | 80 |
| TASK1 | FAM2 | 20 |
| TASK2 | FAM3 | 80 |
| TASK2 | FAM4 | 20 |
| TASK3 | FAM3 | 80 |
| TASK3 | FAM4 | 20 |
| TASK4 | FAM5 | 40 |
| TASK4 | FAM2 | 60 |
TASK_VALUES
Date Task Value Value_family
| dimecres, 1 de gener de 2020 | TASK1 | 120 | 120 |
| dimecres, 1 de gener de 2020 | TASK2 | 30 | 30 |
| dimecres, 1 de gener de 2020 | TASK3 | 200 | 200 |
| dimecres, 1 de gener de 2020 | TASK4 | 40 | 40 |
| dilluns, 10 de febrer de 2020 | TASK1 | 300 | 300 |
| dilluns, 10 de febrer de 2020 | TASK3 | 150 | 150 |
FAMILY
Family Description
| FAM1 | Description family 1 |
| FAM2 | Description family 2 |
| FAM3 | Description family 3 |
| FAM4 | Description family 4 |
| FAM5 | Description family 5 |
TASK
Task Description
| TASK1 | Description task1 |
| TASK2 | Description task2 |
| TASK3 | Description task3 |
| TASK4 | Description task4 |
is this enough information for you? do you need something else?
Thanks !
If values need to change with slicer selection then you cannot use a calculated column, it has to be a measure.
Few changes are needed here:
1) change relationships to what you see below, in visuals and formulas only use relating to tasks use only columns from 'TASK' table as it will propagate filter to TASK_DISTRIBUTION and TASK_VALUES
it removes the bidirectional relationship (which is usually a bad practice, more details here https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/ around 14:30)
2) add this measure (it will work with relationships like above)
Measure =
VAR __ValuesWithPercent =
ADDCOLUMNS ( 'TASK_VALUES', "%", CALCULATE ( SUM ( 'TASK_DISTRIBUTION'[%F] ) ) )
RETURN
SUMX ( __ValuesWithPercent, [Value] * [%] / 100 )
OMG ! It works perfect. It shows that you are an expert. Many thanks.