Forum Discussion
Helper ISingle row Filtering affecting multiple rows
Community SupportHi 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.
Helper IHi 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
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.- lg_analyst
Hi v-yingjl ,
Unofrtunately, my data is meant for confidential use only. I'm attaching a screenshot taken directly from the model, maybe it will help.
Here you can see two Order Numbers, two different orders. Each order can be splitted in different shipments, indeed, first order has 6 different Shipment Number, same for the second one. Each shipment can be assigned in different days, indeed Shipment Number 130882717 has been assigned on 06/04, all other shipments for the first orders have been assigned on 02/04. Same thing for the second order: shipment 130884263 has been assigned on 05/04, all other shipments for the second order have been assigned on 04/04.
My goal is to have a filter allowing me to do the following: should I filter for 02/04 OR 06/04, I should be able to see all rows for the first order. Should I filter for 04/04 OR 05/04, I should be able to see all rows for the second order.
The solution you've brought up really goes in this direction, but it only allows me to se all rows for each order if I filter for the earliest Order Assigned date, which is 02/04 for the first order, 04/04 for the second order. I need all Order Assigned dates for each Order Number to do so.
If you have further questions, please feel free to be more specific about it.
Thanks a lot.
- v-yingjl
Hi lg_analyst ,
Based on the table, you can try to modify the measures like this:
Start Date = DISTINCT('Table'[Order Date])End Date = SUMMARIZE ( ADDCOLUMNS ( 'Table', "End Date", DATE ( YEAR ( [Order Assigned GMT] ), MONTH ( [Order Assigned GMT] ), DAY ( [Order Assigned GMT] ) ) ), [End Date] )visual control = VAR _max = CALCULATE ( MAX ( 'Table'[Order Assigned GMT] ), ALLEXCEPT ( 'Table', 'Table'[Order Number] ) ) RETURN IF ( NOT ( ISFILTERED ( 'Start Date'[Order Date] ) ) && NOT ( ISFILTERED ( 'End Date'[End Date] ) ), 1, IF ( CALCULATE ( MIN ( 'Table'[Order Date] ), ALLEXCEPT ( 'Table', 'Table'[Order Number] ) ) = SELECTEDVALUE ( 'Start Date'[Order Date] ) || DATE ( YEAR ( _max ), MONTH ( _max ), DAY ( _max ) ) = 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.