Forum Discussion
Measure with condition based on date slicer
Hello. I'm trying to make a tricky powerbi dax measure and I'm a bit lost right know because it's been 2 days i'm struggling on this.
I have the following data model in my db:
| task_id | agent_id | imputation_date | exceeding_date | value_before_exceeding | value_after_exceeding |
| id_1 | 592 | 2020-12-17 | 32.63235 | 23.30882 | |
| id_1 | 592 | 2021-01-20 | 32.63235 | 23.30882 | |
| id_1 | 592 | 2021-02-03 | 32.63235 | 23.30882 | |
| id_1 | 592 | 2021-02-08 | 32.63235 | 23.30882 | |
| id_1 | 362 | 2021-03-12 | 130.52941 | 93.23529 | |
| id_1 | 362 | 2021-04-12 | 2021-04-12 | 559.41177 | |
| id_1 | 649 | 2021-04-12 | 2021-04-12 | 559.41177 |
In my powerbi dashboard, i'm trying to display this data in a table with a date slicer (based on imputation_date) and following these conditions:
- If my maximum date range is above exceeding_date: i want to display the sum (for each group of agent and task) of value_after_exceeding
- Else i want to display the sum of value_before_exceeding
- In both case, i want the sum for the date range based on date_imputation
I create a table based on min and max imputation_date:
calendar_imputation = CALENDAR(MIN(valo_fact_imputation[date_imputation]), MAX(valo_fact_imputation[date_imputation]))
My test visual look like this:
(visual 1)
(visual 2)
I have mesaures for min and max slicer date:
min_slicer_date = CALCULATE(FIRSTDATE(calendar_imputation[Date]))max_slicer_date = CALCULATE(LASTDATE(calendar_imputation[Date]))
And so far my measure to calculate date looks like this:
sales =
VAR grouped_by_filter_date =
SUMMARIZE(
FILTER(valo_fact_imputation,
valo_fact_imputation[date_imputation] >= [min_slicer_date] && valo_fact_imputation[date_imputation] <= [max_slicer_date]
),
valo_fact_imputation[task_id],
valo_fact_imputation[agent],
"exceeding", CALCULATE(LASTDATE(valo_fact_imputation[exceeding_date]), ALLEXCEPT(valo_fact_imputation, valo_fact_imputation[task_id], valo_fact_imputation[agent]))
)
return CALCULATE(
IF(
COUNTAX(grouped_by_filter_date, [exceeding]) > 0,
SUMX(
SUMMARIZE(
grouped_by_filter_date,
valo_fact_imputation[task_id],
valo_fact_imputation[agent],
"sum_valo_imput", CALCULATE(SUM(valo_fact_imputation[value_after_exceeding]))),
[sum_valo_imput]
),
SUMX(
SUMMARIZE(
grouped_by_filter_date,
valo_fact_imputation[task_id],
valo_fact_imputation[agent],
"sum_valo_imput", CALCULATE(SUM(valo_fact_imputation[value_before_exceeding]))),
[sum_valo_imput]
)
)
)
My problem is that on visual 1, the sum is made on valo_afeter_exceeding even if it looks like there is no value my "exceeding" column from my SUMMARIZE method.
Any help appreciated and don't hesitate to ask for more infos.
I managed to do what I wanted, here the final measure for those who might be interested:
ca_reel_agent_v2 = VAR grouped_by_filter_date = SUMMARIZE( FILTER(fact_valo_imputation, fact_valo_imputation[date_imputation] >= [min_slicer_date] && fact_valo_imputation[date_imputation] <= [max_slicer_date] ), fact_valo_imputation[task_id], fact_valo_imputation[agent], "min_imputation", CALCULATE(MIN(fact_valo_imputation[date_imputation]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id], fact_valo_imputation[agent])), "max_imputation", CALCULATE(MAX(fact_valo_imputation[date_imputation]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id], fact_valo_imputation[agent])), "exceeding", CALCULATE(FIRSTDATE(fact_valo_imputation[exceeding_date]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id])) ) return CALCULATE( IF( COUNTAX(FILTER(grouped_by_filter_date, [exceeding] >= [min_slicer_date] && [exceeding] <= [max_slicer_date]), [exceeding]) > 0, SUMX( SUMMARIZE( grouped_by_filter_date, "sum_valo_imput", CALCULATE(SUM(fact_valo_imputation[value_after_exceeding]))), [sum_valo_imput] ), SUMX( SUMMARIZE( grouped_by_filter_date, "sum_valo_imput", CALCULATE(SUM(fact_valo_imputation[value_before_exceeding]))), [sum_valo_imput] ) ) )Thanks lbendlin for your time on the topic.
3 Replies
- lbendlinSuper User
If my maximum date range is above exceeding_date: i want to display the sum (for each group of agent and task) of value_after_exceedingplease describe what should happen when the exceeding_date is empty.
Do you want to display the result by agent?
- FloSeurFrequent Visitor
When exceeding_date is empty, the task is not considered as late so yes, it should display the sum of value_before_exceeding for each agent.
In the example dataset, I didn't fill the row of exceeding_date column with null value but it appears like this in db.
For a bit of context, each row correspond to allocation with amount of duration (i didn't put the column in dataset) and when the cumulative sum of allocation exceed task time estimation, i start to fill the column exceeding_date.
I hope my explanations are clear.
- FloSeurFrequent Visitor
I managed to do what I wanted, here the final measure for those who might be interested:
ca_reel_agent_v2 = VAR grouped_by_filter_date = SUMMARIZE( FILTER(fact_valo_imputation, fact_valo_imputation[date_imputation] >= [min_slicer_date] && fact_valo_imputation[date_imputation] <= [max_slicer_date] ), fact_valo_imputation[task_id], fact_valo_imputation[agent], "min_imputation", CALCULATE(MIN(fact_valo_imputation[date_imputation]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id], fact_valo_imputation[agent])), "max_imputation", CALCULATE(MAX(fact_valo_imputation[date_imputation]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id], fact_valo_imputation[agent])), "exceeding", CALCULATE(FIRSTDATE(fact_valo_imputation[exceeding_date]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id])) ) return CALCULATE( IF( COUNTAX(FILTER(grouped_by_filter_date, [exceeding] >= [min_slicer_date] && [exceeding] <= [max_slicer_date]), [exceeding]) > 0, SUMX( SUMMARIZE( grouped_by_filter_date, "sum_valo_imput", CALCULATE(SUM(fact_valo_imputation[value_after_exceeding]))), [sum_valo_imput] ), SUMX( SUMMARIZE( grouped_by_filter_date, "sum_valo_imput", CALCULATE(SUM(fact_valo_imputation[value_before_exceeding]))), [sum_valo_imput] ) ) )Thanks lbendlin for your time on the topic.