Forum Discussion

Kreator's avatar
Kreator
New Member
2 years ago
Solved

Dynamic Summarize/Filter Formula

I'm working on visualizing the updated user journey, and I've come up with a formula that provides accurate results when the full date range is selected. However, I've noticed that the formula doesn't adapt correctly when the date range is narrowed down. Any insights would be greatly appreciated.


Formula
COUNTROWS(   
    SUMMARIZE(
        FILTER(
            'Table',
            'Table'[date] = CALCULATE(MAX('Table'[date]), ALLEXCEPT('Table', 'Table'[Customer_ID], 'Table'[Journey_ID]))
        ),     
        'Table'[Customer_ID]  
    )
)
Formula Explanation
I'm filtering the table to have the max date per each customer and journey, then I'm counting the customers, the missing part is to have the max dates that are within the selected date range in the slicer

The case that works (only yellow lines are shown when full date range is selected)


The case that I need help with (if only grey dates are selected I want to show the green lines only)


"DAX"
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Kreator

     

    Based on your description, please try the following steps:

     

    1. Create a calculated table as follows.

    Date = VALUES('Table'[Date])

     

    2. Create several measures as follow.

    MaxDate = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Journey_ID], 'Table'[Customer_ID] ),
        [Date] >= MIN ( 'Date'[Date] )
            && [Date] <= MAX ( 'Date'[Date] )
    )
)
    Measure = IF(SELECTEDVALUE('Table'[Date]) = [MaxDate], 1, 0)
    count = COUNTROWS(SUMMARIZE(FILTER('Table', [Date] = [MaxDate]),'Table'[Journey_ID]))

     

    3. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

     

    Is this the result you expect?

     

    Best Regards,
    Community Support Team _Yuliax
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Kreator

     

    Based on your description, please try the following steps:

     

    1. Create a calculated table as follows.

    Date = VALUES('Table'[Date])

     

    2. Create several measures as follow.

    MaxDate = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Journey_ID], 'Table'[Customer_ID] ),
        [Date] >= MIN ( 'Date'[Date] )
            && [Date] <= MAX ( 'Date'[Date] )
    )
)
    Measure = IF(SELECTEDVALUE('Table'[Date]) = [MaxDate], 1, 0)
    count = COUNTROWS(SUMMARIZE(FILTER('Table', [Date] = [MaxDate]),'Table'[Journey_ID]))

     

    3. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

     

    Is this the result you expect?

     

    Best Regards,
    Community Support Team _Yuliax
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.