Forum Discussion
Calculated field and filtering
Hello,
I have a doubt with a calculated field.
I have a TASK table with this information:
TASK_VALUES (date, task, value)
1.2.2020, TASK1, 120
1.2.2020, TASK2, 30
1.2.2020, TASK3, 200
1.2.2020, TASK4, 40
10.2.2020, TASK1, 300
....
And a second table with TASK_DISTRIBUTION
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%
And I would like to have a new calculated filed in TASK_VALUES table in this way:
Value_family = SUMX(FILTER(TASK_DISTRIBUTION;TASK_VALUES[Task]=TASK_DISTRIBUTION[Task]);TASK_DISTRIBUTION[%F]*[Value]/100)
It works without report filters, but when I filter by “family” this filter is not taken into account and the result is the same that without filter.
I must answer questions like:
- “value for family FAM1 on day 1.1.2020” --> 96
- “value for family FAM2 on day 1.1.2020” --> 48 (24+24)
- “family distribution values on day 1.1.2020” --> FAM1-96, FAM2-48, FAM3-184, FAM4-46, FAM5-16 (Total 390)
What change should I have to make in the formula?
Thanks in advance!
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 )
5 Replies
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 )
Well, actually you can do that.
Drag to filter. Select the "Condition" tab. Suppose your paramater is named "Chosen department". You could create a condition like:
SUM(IF [department] = [Chosen department] THEN 1 END) > 0
This will basically get all the clients that have at least 1 sale on that chosen department
