Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure to filter table - related tables

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 DateBeginDateEndDate
101/11/2019 1/11/2019 1/11/20191/11/20191/11/2019
2001/12/2019 1/12/2019 1/12/20191/12/20191/14/2019
5001/13/2019 1/13/2019 1/13/20191/12/20191/14/2019
3001/14/2019 1/14/2019 1/14/20191/12/20191/14/2019
501/15/2019 1/15/2019    
101/16/2019 1/16/2019    
11/17/2019 1/17/2019    
51/18/2019 1/18/2019    
41/19/2019 1/19/2019 1/19/20191/19/20191/19/2019
41/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:

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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,

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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,