Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
FloSeur
Frequent 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_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:

FloSeur_1-1718277506451.png

(visual 1)

FloSeur_2-1718277520462.png

(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.

1 ACCEPTED SOLUTION
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.

View solution in original post

3 REPLIES 3
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.

lbendlin
Super User
Super 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_exceeding

please describe what should happen when the exceeding_date is empty.

 

Do you want to display the result by agent?

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.