Measure to filter table - related tables

[Anonymous]

I have tables set up like the following, where calendar_date is the joining field from my date table to the other three tables. The table on the left I've displayed as summarized data, there are records for each event.

In PBI the user is able to use a slicer to adjust the 'max' calendar_date. However, on this one report page (only instance of this, elsewhere we don't want this), I need the visuals to only load the most recent 'beginDate' of the storm table.

Count(events)	event_date		Calendar_date		Storm Date	BeginDate	EndDate
10	1/11/2019		1/11/2019		1/11/2019	1/11/2019	1/11/2019
200	1/12/2019		1/12/2019		1/12/2019	1/12/2019	1/14/2019
500	1/13/2019		1/13/2019		1/13/2019	1/12/2019	1/14/2019
300	1/14/2019		1/14/2019		1/14/2019	1/12/2019	1/14/2019
50	1/15/2019		1/15/2019
10	1/16/2019		1/16/2019
1	1/17/2019		1/17/2019
5	1/18/2019		1/18/2019
4	1/19/2019		1/19/2019		1/19/2019	1/19/2019	1/19/2019
4	1/20/2019		1/20/2019

I use the storm beginDate topN filter set to 1 by latest 'storm date'. Thus if the user has the calendar date slider filtered to 1/16/2019 (green), we want to add up the count of events that happened during the last storm beginDate of 1/12/2019 (blue), so the total would be 1000 count(events). I use topN beginDate by latest StormDate in a table visual where I have Calendar_date showing and it works PERFECTLY.

But, I have lots of visuals here that don't have calendar_date showing on them. Even if I put calendar_date in the visual level filters, it doesn't work. but as soon as I put calendar_date on the chart (as an axis) it works perfectly. I can sort of make it work by putting year on the axis and the other fields I want to show on the legend, but that doesn't look as good as just having the column labels where they should be on the axis. How do I get this visual to evaluate the dates in order to look at the filter on the beginDate?

The default behavior when this screen opens up is it shows the table visual (which works perfectly), but the bar chart doesn't work (even though I put year on it trying to make it evaluate).

When clicked it works great, I just need it to do this automatically:

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Event Count =
VAR latestStorm =
    MAX ( 'Event Table'[Storm Date] )
VAR begin =
    CALCULATE (
        MIN ( 'Event Table'[BeginDate] ),
        FILTER (
            ALLSELECTED ( 'Event Table' ),
            'Event Table'[Storm Date] = latestStorm
        )
    )
VAR end =
    CALCULATE (
        MIN ( 'Event Table'[EndDate] ),
        FILTER (
            ALLSELECTED ( 'Event Table' ),
            'Event Table'[Storm Date] = latestStorm
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Event Table'[Event ID] ),
        FILTER (
            ALLSELECTED ( 'Event Table' ),
            'Event Table'[Storm Date] >= begin
                && 'Event Table'[Storm Date] <= end
        )
    )

Best regards,