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.
