Grouping fails with slicer dependent measure in filters

[zvyezdan]

Hello,

I have a table with a date column and also a date slicer. I add a measure to the table that tags rows with 0 or 1 depending whether the date is in the period (e.g. year) selected in a slicer or not.

When I display that data in the table visual it shows as expected. For example, slicer has 2024 selected. Rows with the "term date" in 2024 are tagged with 1 "in period", just as expected:

Next I want to do is display a pie chart: count("id") group by "reason", for the selected period in the slicer. One of the filters I have is of course "in period" = 1, that filter is the reason I created the measure in the first place.... to be able to connect the date slicer and data. I cannot otherwise just add the "term date" to filters and compare it to the date slicer.

However, that filter does not behave as expected at all....counts are incorrect, "reason"s are not all accounted for etc.

Here's the measure code for reference

in_period =
    VAR strt_date = MAX(employee[strt_date])
    VAR term_date = MAX(employee[term_date])
    VAR period_start = FORMAT(MINX(dates, dates[datetime]), "yyyy-MM-dd")
    VAR period_end = FORMAT(MAXX(dates, dates[datetime]), "yyyy-MM-dd")
    var result = IF(strt_date < period_start && period_start <= term_date && term_date <= period_end, 1, 0)
RETURN
    result

Are there any obvious things I am doing or expecting that are plain wrong here? Why would the table visual be correct but grouping visual not?

Thx all!

[zvyezdan]

Naveen your question about relationship prompted me to double check them... and both "start date"->"dates" and "term date"->"dates" were non-active.

Activating "term date"->"dates" relationship made it work. So, your question was well placed!

However, still not out of the woods as I have 2 conditions to make good on... "term date" is now solved by activating the relationship. The remaining condition on "start date" states that it has to be before the selected period (i.e. date slicer selection).

Which means now I am back to tagging the rows with whether the employee's "start date" is before the slicer selected period or not.

start_before_period = 
    VAR strt_date = MAX(employee[start_date])
    VAR period_start = FORMAT(MINX(dates, dates[datetime]), "yyyy-MM-dd")
    var result = IF(strt_date < period_start, 1, 0)
RETURN
    result

Adding that measure as a filter "start before period = 1" to the grouping visual (e.g pie-chart) produces the same issue. The condition: "start date" has be be before the selected period cannot be expressed with an active relationship... also one can have only one active relationship apparentlly.

Also need to check that other results in the calc table are still correct after activating the relationship.

[NaveenGandhi]

@zvyezdan 

Make change to your measure as below, The inclusion of term date in the condition is to neglect false true(all start date before period start being pulled and not just the one's within term date)

start_before_period =

    VAR strt_date = MAX('Table'[Start Date])

    VAR term_date = MAX('Table'[term date])

    VAR period_start = CALCULATE(min('date'[Date]),ALLSELECTED('date'))

    var result = IF(and(strt_date < period_start,period_start <= term_date), 1, 0)

RETURN

    result

Also make sure the start date and term date is marked as date, and instead of using format for period start, just make it date data type.

This should resolve the issue. Attached file has sample scenario.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[zvyezdan]

Hi Neveen, thanks for helping out. I have loaded up the solution you attached, then modified it a bit to better reflect the issue. It's great that we can now replicate the problem with a sample model.

I added a "Reason" column to the model, filled in some data, then added 2 (grouping) visuals - pie chart and bar chart. These charts do not work with the calculated start-before column. However, the data table and the bar chart as you had then - do work. They do work in my original model too.

Here's the updated Date2.pbix, linked, as I cannot attach it to the message. If you have time check it out.

Thanks!

[NaveenGandhi]

Hi @zvyezdan 

Don't you have relationship between the date table and the Id, reason table?

If you provide the sample data and snapshot of what your expected output it would be easier to resolve this.

Regards,
NG

[zvyezdan]

Here's what I expect to see as per table snapshot given in previous post

This is by stating the filter "term date" starts with 2024 (thus emulating the date slicer selection being 2024).

I expected to see the same by using "in period = 1" filter, as below

Numbers are dfferent so I am missing something here.

This is to depict the expectation vs. the actual outcome.... and you can see the filters used.

Also I do have a relationship between the "dates" tables and the "employee"'s table  "start date" and "term date". However, the relationship does not really reflect when working the visual slicer and data visuals. Maybe that should work in the first place too.