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_idagent_idimputation_dateexceeding_datevalue_before_exceedingvalue_after_exceeding
id_15922020-12-17 32.6323523.30882
id_15922021-01-20 32.6323523.30882
id_15922021-02-03 32.6323523.30882
id_15922021-02-08 32.6323523.30882
id_13622021-03-12 130.5294193.23529
id_13622021-04-122021-04-12 559.41177
id_16492021-04-122021-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:

  1. 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
  2. Else i want to display the sum of value_before_exceeding
  3. 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

  • 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

    please describe what should happen when the exceeding_date is empty.

     

    Do you want to display the result by agent?

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

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