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 - Sorry my bad, missed the aggreagtion.
SUMX(VALUES('CBAM_Goods_InScope'[HsCode]), SUM( 'CBAM_Goods_InScope'[SupplementaryUnitQty] ) )
I was typing too quickly!
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
This I have already checked it. I need summarized table not the measure...Since I need to use the summarized table for rest of the calculation... Expected result it same as you see in the SQL query...
- mark_endicott
Anonymous - 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!