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 - Ok you were not specific enough. Try this:
SUMMARIZE('CBAM_Goods_InScope','CBAM_Goods_InScope'[HsCode], "TotalSupplementaryUnitQty", SUM( 'CBAM_Goods_InScope'[SupplementaryUnitQty] ) )
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
mark_endicott I need specific result set only. So below condition is must considered dynamically before summarizing the table..
HAVING SUM([CustomsValue]) > 150 (150=will be based in the parameter)
- mark_endicott
Anonymous - You cannot do this with a calculated table. They are evaluated and created at the point of the report refresh. They do not change their results in response to the use of a parameter.
However, if you create the table with my DAX above, you can use your Visual Filter on the visuals you wish to ammend to display only the results from the table that match your Threshold condition.
Just use the visual filter measure like below:
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
mark_endicott After creating the table and applying the visual filter it is not working hence I have created this post...
It works till the BOLD part of the SQL as I specified in the post initially. Outer query should work based on the inner query that is what the problem is.
mark_endicott Hope you have got my concern? Let me know if you have any questions...
- mark_endicott
Anonymous - Without a screenshot or more information I cannot tell what is "not working".
To be honest I don't think you are approaching this in the best way. I don't know why you need to create a table in DAX at all. You can do this with a measure that will only populate a SupplementaryUnitQty total against the rows of HsCode where the threshold condiction is met.
The DAX for this is below, where it creates a virtual table of only the EntryIdentifiers that meet the Threshold condition.
This measure can be placed against the HsCode column and will dynamically populate as you change the parameter.
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 ) )If you can explain why you need to populate a DAX table, I might be able to provide more help.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!