Forum Discussion
FloSeur
2 years agoFrequent Visitor
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 ag...
- 2 years ago
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.
FloSeur
2 years agoFrequent 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.