Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dax query using calculated buckets as filters

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 cha...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi Anonymous

     

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
OwenAuger
Icon for Super User rankSuper User
8 years ago

Hi Anonymous

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Good stuff Owen.

     

    Thats relieved a huge headache.

     

    Thanks for taking the time to look at that.