Forum Discussion
Grouped summaries with conditional filtering
Anonymous - If the answers you have given me are true and all your data lives in the CBAM_Goods_InScope table then the DAX for a Dynamic measure I gave you earlier works. I have copied it below again:
VAR Threshold = [SelectedThreshold] VAR HighValueEntries = CALCULATETABLE ( VALUES ( 'CBAM_Goods_InScope'[EntryIdentifier] ), FILTER ( ADDCOLUMNS ( VALUES ( 'CBAM_Goods_InScope'[EntryIdentifier] ), "@CustomsValue", CALCULATE ( SUM ( 'CBAM_Goods_InScope'[CustomsValue] ) ) ), [@CustomsValue] > Threshold ) ) RETURN CALCULATE ( SUM ( 'CBAM_Goods_InScope'[SupplementaryUnitQty] ), KEEPFILTERS ( 'CBAM_Goods_InScope'[EntryIdentifier] IN HighValueEntries ) )I will repeat, you cannot do this dynamically within a physical DAX calculated table, because they do not re-calculate every time a parameter changes, a.k.a they are static.
If you want this to be dynamic, then you will have to use a measure. Use the DAX I have given you above. It creates a virtual table, based on the selectedThreshold, and the SupplementaryUnitQty is then calculated over this table. It is the most optimal and performance efficient version of this calculation you will get.
I am attaching my file so you can investigate it working, and below are some screenshots to show it dynamically calculating based on the value in the slicer.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Super UserAnonymous Anonymous - I have already done this and attached the file for you to use. See below for the post it is further up the chain.
Anonymous - if the file I have already supplied you has not resolved your issue, please can you explain why?
Hi Anonymous,
mark_endicott ,thank you and really appreciate the detailed support and working file you have provided.
Yes, completely agree with your approach here. As you rightly mentioned, calculated tables won’t respond to slicer/parameter changes at runtime. The dynamic measure with virtual filtering using TREATAS and filtered EntryIdentifiers is the right and optimal solution in this case.
Anonymous Please do revisit Mark’s earlier post and test the attached PBIX. If you are still seeing differences between your SQL output and Power BI results, could you kindly share what exactly is not matching? That’ll help us assist more precisely.
Best regards,
Harshitha .
- mark_endicott
Anonymous - I have supplied a working solution in DAX. Asking why numbers do not match to a SQL query is a dangerous question as there could be any number of reasons outside Power BI for this.
Please can you accept my solution.
Hi Anonymous,
Just wanted to check if you had the opportunity to review the suggestion provided by Anonymous
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You.
Harshitha.
Hi Anonymous ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.
Thank you.Hi Anonymous,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Harshitha.Thanks! Sorry for late response. I am back today as there was family emergency. Will check and update in couple of days.
Hi Anonymous,
We really appreciate your efforts and for letting us know the update on the issue.
Please continue using fabric community forum for your further assistance.
Regards,
Harshitha.Hi Anonymous,
Could you please let us know the ETA by when you will be able to test the provided solution? This will help us to know when we can follow up. If you encounter any challenges during implementing the provided solution, please inform us and we’ll be glad to assist.
Regards,
Harshitha.Anonymous Pls close this thread. This is something I wont be working on it now. Thanks