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!
I dont need result in Matrix I need it as a table or virtual table which can be used for further part of calculation...
Super UserHi Anonymous,
Okay got it, try below solutions
Below virtual table filters rows dynamically and can be used within a measure
VAR Threshold = [SelectedThreshold]
VAR FilteredTable =
FILTER(
ADDCOLUMNS(
SUMMARIZE('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier]),
"TotalValue", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))
),
[TotalValue] > Threshold
)
VAR ResultTable =
CALCULATETABLE(
SUMMARIZE(
'CBAM_Goods_InScope',
'CBAM_Goods_InScope'[HsCode],
"TotalSupplementaryUnitQty", SUM('CBAM_Goods_InScope'[SupplementaryUnitQty])
),
TREATAS(
SELECTCOLUMNS(FilteredTable, "EntryIdentifier", 'CBAM_Goods_InScope'[EntryIdentifier]),
'CBAM_Goods_InScope'[EntryIdentifier]
)
)
RETURN
ResultTable
If You Want a Physical Calculated Table, use below DAX
Filtered_HSCode_SuppQty =
VAR Threshold = [SelectedThreshold]
VAR ValidEntryIds =
FILTER(
ADDCOLUMNS(
SUMMARIZE('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier]),
"TotalCustomsValue", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))
),
[TotalCustomsValue] > Threshold
)
RETURN
SUMMARIZE(
FILTER(
'CBAM_Goods_InScope',
'CBAM_Goods_InScope'[EntryIdentifier] IN
SELECTCOLUMNS(ValidEntryIds, "EntryIdentifier", 'CBAM_Goods_InScope'[EntryIdentifier])
),
'CBAM_Goods_InScope'[HsCode],
"TotalSupplementaryUnitQty", SUM('CBAM_Goods_InScope'[SupplementaryUnitQty])
)
If you want to use it to get the total sum across filtered EntryIdentifiers
TotalSupplementaryUnitQty_Measure :=
VAR Threshold = [SelectedThreshold]
VAR ValidEntries =
FILTER(
ADDCOLUMNS(
SUMMARIZE('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier]),
"TotalVal", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))
),
[TotalVal] > Threshold
)
VAR Result =
CALCULATE(
SUM('CBAM_Goods_InScope'[SupplementaryUnitQty]),
TREATAS(
SELECTCOLUMNS(ValidEntries, "EntryIdentifier", 'CBAM_Goods_InScope'[EntryIdentifier]),
'CBAM_Goods_InScope'[EntryIdentifier]
)
)
RETURN
Result
grazitti_sapna Thanks and I tried this... Summarize will not work based on the dynamic parameter as I mentioned in my post earlier... It is always considering 150 which is default value not the value which is getting passed...
Hi Anonymous ,
You're correct when we use SUMMARIZE in a calculated table, it doesn’t pick up the dynamic slicer value like [SelectedThreshold]. It only takes the default value at the time of refresh. To make it work based on what the user selects in the slicer, it’s better to use a measure-based virtual table. That method will react properly to the slicer and can be used in further calculations also.
If this answer was helpful, please consider marking it as Accepted Solution and giving a Kudos, it helps the community.
Best Regards,
Harshitha.Anonymous Thanks for your response. Can you please let me know how to create the measure based virtual table to filter the data based on my requirement.