Forum Discussion

lg_analyst's avatar
lg_analyst
Icon for Helper I rankHelper I
5 years ago

Single row Filtering affecting multiple rows

Hi all,

 

I'm struggling satysfying a business request on a dashboard here. I need to display some information about shipments. Each Order ID can be shipped in a single Shipment ID or splitted in more Shipment IDs. Each Shipment ID has a Start Date (shipment is assigned) and an End Date (shipment is deivered).

 

The request is: Let's imagine Order ID A is splitted in Shipment IDs x, y, z. These three shipments have all different Start Dates. When a Start (or End) date is filtered, I need to show all rows for Order ID A, event though the Start Date I'm slicing only contains Start Date of Shipment ID x (or y or z).

 

Other details: There is no fixed or maximum number of Shipment IDs the same Order ID can be splitted into. Start and End Date slicers can be separated on the visualization, so the problem can be treated "singularly".

 

Here a quick illustration of what should happen by filtering Start Date 01/01/2021 (Image 1) and End Date 10/01/2021 (Image 2)

 

Image 1

 

Image 2

 

 

Any help? Thanks!

 

Need Help
Tips and Tricks

10 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    lg_analyst,

     

    Try this solution. It requires two disconnected date tables (no relationships), with a slicer based on each.

     

    1. Data model:

     

     

    2. Measure:

     

    Display Row = 
VAR vStartDate =
    SELECTEDVALUE ( StartDate[Date] )
VAR vEndDate =
    SELECTEDVALUE ( EndDate[Date] )
VAR vRowCountStartDate =
    CALCULATE (
        COUNTROWS ( Shipment ),
        ALLEXCEPT ( Shipment, Shipment[Order ID] ),
        Shipment[Start Date] = vStartDate
    )
VAR vRowCountEndDate =
    CALCULATE (
        COUNTROWS ( Shipment ),
        ALLEXCEPT ( Shipment, Shipment[Order ID] ),
        Shipment[End Date] = vEndDate
    )
VAR vResult =
    SWITCH (
        TRUE (),
        // if Start Date and End Date are not specified
        ISBLANK ( vStartDate ) && ISBLANK ( vEndDate ), 1,
        // if an order has at least one row corresponding to the selected date
        vRowCountStartDate > 0
            || vRowCountEndDate > 0, 1
    )
RETURN
    vResult

     

    3. Add measure [Display Row] to visual filter (set to "is 1"):

     

     

    4. Result:

     

    -----------------------------------------------------------

    -----------------------------------------------------------

     

     

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi lg_analyst ,

    Not certain what is your expected output but seems like you can try to  use the Order ID as a slicer to filter it just based on the image.

     

    Or you can consider sharing more details about it for further discussion.

     

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

    • lg_analyst's avatar
      lg_analyst
      Icon for Helper I rankHelper I
      5 years ago

      Hi v-yingjl ,

       

      My expected output is a table such as the one I've screenshotted, it's going to be very similar in the visualization as well.

       

      I cannot filter through Order IDs, the request is being able to use Start Date and End Date in the way I've shown. Basically for each Start Date (or End Date) the user filters, I need to be able to show all the rows related to Order IDs that have at least one Start Date in the dates the user filters. My difficulty resides in the fact that I need to show rows belonging to other Start Dates as well, if they have an Order ID in common. 

       

      Here's another example, basically I should be able to visualize both rows of the table wether I'm filtering for 01/01/2021 or 02/01/2021. The user needs to filter through Start or End Date, not using Order ID's list. Rows could be more: each Order ID might have more than two Shipment ID linked to it, with no precise limit. 

       

      Order IDShipment IDStart DateEnd Date
      AXXX01/01/202110/01/2021
      AYYY02/01/202111/01/2021

       

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi lg_analyst ,

        Extract start date and end date as a single calculated table separately first.

         

        Start Date = DISTINCT('Table'[Start Date])
End Date = DISTINCT('Table'[End Date])

         

        Create a measure like this, put it in the table visual filter and set its value as 1:

         

        visual control = 
IF (
    NOT ( ISFILTERED ( 'Start Date'[Start Date] ) )
        && NOT ( ISFILTERED ( 'End Date'[End Date] ) ),
    1,
    IF (
        CALCULATE (
            MIN ( 'Table'[Start Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Order ID] )
        )
            = SELECTEDVALUE ( 'Start Date'[Start Date] )
            || CALCULATE (
                MIN ( 'Table'[End Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Order ID] )
            )
                = SELECTEDVALUE ( 'End Date'[End Date] ),
        1,
        0
    )
)

         

         

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