Dax query using calculated buckets as filters

[andrmh]

Hi All,

I'd have thought this would be a common thing, but im having trouble with it.

I have a report, containing a pie chart, a date slicer, and a table listing transactions.

The pie chart lists Order counts by age (in weeks) 1 through 6+ , based on the max selected date in the date range.

These buckets are calculated as follows:
 

Week 1 = CALCULATE( count(OrdersTable[OrderNumber]), FILTER( OrdersTable, DATEDIFF(OrdersTable[dateCreated], [MaxSelectedDate],WEEK) = 0))

... through to the 6th week being:

Week 6+ = CALCULATE( count(OrdersTable[OrderNumber]), FILTER( OrdersTable, DATEDIFF(OrdersTable[dateCreated], [MaxSelectedDate],WEEK) >= 5))

This gives me 6 measures in total, each dragged onto the chart as a separate measure. Note - MaxDate is of my calendare/date dim table and represents the max date off the slicer. So my bucketed values do move pending the selected date.

My Transactions table visual, simply lists the records from OrdersTable.

What i want to be able to do, is click on a slice of the chart visual, and for it to filter the transactions list to show only the records that comprise of the pie slice selected.

I have a feeling I may have taken the wrong approach here in how I calculate my buckets. But need some guidance.

Keep in mind i do have the date slicer as previously mentioned, and if it helps, my report is in DirectQuery mode.

Thanks,

[v-yulgu-msft]

Hi @andrmh,

If there existing a relationship between calendare/date dim table and OrdersTable, please remove this relationship for a test.

Regards,

Yuliana Gu

[andrmh]

Thanks for the reply Yuliana, however this does not correct the issue.

the top of the report looks as follows:

 

The process is:

1) because of the presence of the date slicer, the 'buckets' are recalculated after the date is altered.

2) Once the date filter is altered, the pie char values, adjust accordingly.

3) On clicking on a piece of the pie chart, I wish to show a table below it, which represents the transactions that represent that piece of the pie. (ie, filter/slice the data)

I am however calculating each 'slice' as a separate measure, and placing onto the pie visual. So 1 slice of the pie, is actually representative of 1 dax measure.

This is why the default slicer behavior doesnt work. The table visual with the transactions has nothing to slice.... The measure is across all transactions.

What i need to be able to do, is have a way where I create a new measure, perhaps with textual values (1 Week, 2 Weeks.... etc) based on the buckets and allow it to adjust.

Normally I would do this at the source (data) level. But because the buckets are moving according to the slicer, im stuck...

I also cant use a calculated column, as I'm using direct query mode and it wont allow usage of min/max in calculated columns.

Effectively I can see im asking for a measure that is for all intended purposes actually a column...

EDIT:
I can/have been able to create a measure based on a solution found in another thread on this orum:
Note [MaxSelectedDate] is taken from my date dimension, and represents the maximum selected slicer value:

Weeks Old = 
SWITCH (
TRUE (),
MIN(Orders[dateCreated]) <= [MaxSelectedDate], DATEDIFF( MIN(Orders[dateCreated]), [MaxSelectedDate] , WEEK ),
MIN(Orders[dateCreated]) > [MaxSelectedDate], DATEDIFF( MIN(Orders[dateCreated]), [MaxSelectedDate] , WEEK ) * -1
)

This allows me to get the correct age in weeks from the slicer value showing up in the transaction list, however does not help my pie chart scenario.

If I were to create a bucket lookup table, Which no relationships were established, and fetch a single value (say a name/label) out based on the [Weeks Old] measure above, could this give me the scenario im after?
If so how would I go about it?

Hope this further explains.

Thanks,

Andrew

[andrmh]

Having dug deeper with this, what I'd like to be able to do is quite similar to here:

https://www.daxpatterns.com/dynamic-segmentation/

Only, My statement needs to be doing the segment range, based on age in weeks.

So my segments table looks like:

Any my measure would look something like:

Segments Test = 
IF (
    ISFILTERED ( Segments[Name] ),
    CALCULATE (
        count(Orders[OrderNumber]),
        FILTER (
            VALUES ( Orders[OrderNumber] ),
            COUNTROWS (
                FILTER (
                    Segments,
                        DATEDIFF(min(Orders[dateCreated]), [MaxSelectedDate],WEEK) >= Segments[MinValue]
                        && DATEDIFF(max(Orders[dateCreated]), [MaxSelectedDate],WEEK) < Segments[MaxValue]
                )
            ) > 0
        ), Orders[IsOpen]=1
    ),
    Calculate(count(Orders[OrderNumber]), Orders[IsOpen]=1)
)

Now this is not giving me the desired result.

I believe this is due to the usage of min/max aggregates in the filter above.

This measure will list the total for each segment as the same value.

Again, I am using Direct Query mode over SQLServer. I cannot use calculated columns containing aggregates.

Normally I would get SQL to do the grunt work for me.
But.... Because of the date slicer:

It means my bucket labels can shift, based on the 'MaxDate" selected.

Any help at all here would be appreciated. It feels like its direct query blocking my progress here, but surely theres still a solution.

Thanks,

Andrew

[OwenAuger · Accepted solution]

Hi @andrmh

I agree Dynamic Segmentation is the way to go.

Try this adjusted version of your measure:

Segments Test =
IF (
    ISFILTERED ( Segments[Name] ),
    VAR MaxDate = [MaxSelectedDate]
    RETURN
        CALCULATE (
            CALCULATE ( COUNT ( Orders[OrderNumber] ), Orders[IsOpen] = 1 ),
            FILTER (
                VALUES ( Orders[dateCreated] ),
                COUNTROWS (
                    FILTER (
                        Segments,
                        DATEDIFF ( Orders[dateCreated], MaxDate, WEEK ) >= Segments[MinValue]
                            && DATEDIFF ( Orders[dateCreated], MaxDate, WEEK ) < Segments[MaxValue]
                    )
                )
                    > 0
            )
        ),
    CALCULATE ( COUNT ( Orders[OrderNumber] ), Orders[IsOpen] = 1 )
)

I have made a few changes highlighted in red.

The critical one is to filter VALUES ( Orders[dateCreated] ) rather than VALUES ( Orders[OrderNumber]), since we want to iterate through each value of dateCreated value and determine whether it is included or excluded. The previous MIN/MAX approach could have worked as well if they were wrapped in CALCULATE, but I think this way is simpler.

Does this give the right result?

Regards,

Owen

[andrmh]

Good stuff Owen.

Thats relieved a huge headache.

Thanks for taking the time to look at that.