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...
Need Help
Tips and Tricks
  • FloSeur's avatar
    FloSeur
    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.

lbendlin's avatar
lbendlin
Super User
2 years ago
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
2 years ago

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.