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 m...
Need Help
Tips and Tricks
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.

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

        Hi v-yingjl ,

         

        Thanks for you answer. I've attempted using the .pbix you've attached but the model doesn't work on my pc. I find it pretty weird, considering it works on yours. Might it be a PBI version issue? It seems strange to me but it's the only guess I can make.

         

        By the way, this is exactly the objective I want to achieve. 

         

        Thanks