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 ,

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

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

    Hi lg_analyst ,

    I have updated the sample file again after I updating the version of power bi desktop(June 2021), please check and try it again.

     

    If it still has the same issue, you can try to create measures by yourself refering to the previous formulas in your report, the source table is just like your posted pictures initially.

     

    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 your answer. You model is now working, but it doesn't 100% provide what I need. Indeed, your solution only works when selecting the earliest Start (or End) Date for the same Order ID.

      Taking into account the attached table in your reply, my goal is to have all rows having Order ID = A even if I filter for Start Date '01/02/2021'. With your solution, this is only possible by filtering Start Date '01/01/2021'. As far as I'm understanding, the logic you use building visual_control measure cannot work in any other way tho.

       

      Thanks,

      Enrico

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

        Hi lg_analyst ,

        Based on the sample table, if the dates are changed to be other dates, what would be your expected output? Could you please 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.