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 )
How does your data model look like, are there any relationships between these tables? Do you have a table with unique Tasks linked to both of these? If not
Also can you add change sample tables to format that can be copied to PowerBI? Like this (just copy and paste into the post window).
| Column1 | Column2 |
| A | 1 |
| B | 2.5 |
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.