Forum Discussion
Grouped summaries with conditional filtering
We would like to achieve the below using the import mode in Power BI. We have already achieved the one in BOLD using visual filter. I need your help to achieve the outer query. Please kindly help
SELECT
HsCode,
SUM(SupplementaryUnitQty) AS TotalSupplementaryUnitQty
FROM
[dbo].[CBAM_Goods_InScope]
WHERE
[EntryIdentifier] IN (
SELECT [EntryIdentifier]
FROM [dbo].[CBAM_Goods_InScope]
GROUP BY [EntryIdentifier]
HAVING SUM([CustomsValue]) > [Parameter]
)
Visual Filter which is currently used:
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!
36 Replies
Anonymous - If you just need DAX for the outer query it's this:
SUMX(VALUES('CBAM_Goods_InScope'[HsCode]), 'CBAM_Goods_InScope'[SupplementaryUnitQty] )The visual filter then provides the rest.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Anonymous - 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!
Hi danextian,
Try using below DAX for outer query:-
Total SupplementaryUnitQty (Filtered) =
VAR Threshold = [SelectedThreshold]
RETURN
CALCULATE(
SUM('CBAM_Goods_InScope'[SupplementaryUnitQty]),
FILTER(
'CBAM_Goods_InScope',
CALCULATE(
SUM('CBAM_Goods_InScope'[CustomsValue]),
ALLEXCEPT('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier])
) > Threshold
)
)Use it in table matrix as
In a table or matrix visual:
-
Put
'CBAM_Goods_InScope'[HsCode]in rows -
Add this new measure
Total SupplementaryUnitQty (Filtered)in values
This will return the correct sum per HsCode based on your SQL condition.
You can also apply filtering based on the show row measure, Use below DAX
ShowRow =
VAR Threshold = [SelectedThreshold]
VAR TotalForEntry =
CALCULATE(
SUM('CBAM_Goods_InScope'[CustomsValue]),
ALLEXCEPT('CBAM_Goods_InScope', 'CBAM_Goods_InScope'[EntryIdentifier])
)
RETURN
IF(TotalForEntry > Threshold, 1, 0)Then use
ShowRow = 1as a page-level filter if needed.🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi Anonymous,
Have you tried this solution?
Hi 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
ResultTableIf 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
-
