Forum Discussion

FloSeur's avatar
FloSeur
Frequent Visitor
2 years ago
Solved

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...
  • FloSeur's avatar
    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.