Grouped summaries with conditional filtering

[pbiforum_123]

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:

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)

[grazitti_sapna]

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 = 1 as 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!

[pbiforum_123]

I dont need result in Matrix I need it as a table or virtual table which can be used for further part of calculation...

[grazitti_sapna]

Hi @pbiforum_123,

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

[pbiforum_123]

@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...

[grazitti_sapna]

Hi @pbiforum_123,

Have you tried this solution?

[mark_endicott]

@pbiforum_123 - 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!

[pbiforum_123]

@mark_endicott  Thanks for your time but this is not working. I am getting this error.

[mark_endicott]

@pbiforum_123 - 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!

[pbiforum_123]

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]

@pbiforum_123 - 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!

[pbiforum_123]

@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]

@pbiforum_123 - 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!

[pbiforum_123]

@mark_endicott Hope you have got my concern? Let me know if you have any questions...

[mark_endicott]

@pbiforum_123 - 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!

[pbiforum_123]

@mark_endicott Thanks a lot for your help...

I agree with you table creation may not required but virtually if it can create the table which will be same as the result of the below query then it is fine. I will check this solution and will let you know...

I used the code that you have shared... Number of records in the sql and the number of records in power bi table is not matching...Also see the measure value it is same for all..

[mark_endicott]

Is the column 'HsCode' in the 'CBAM_Goods_InScope' table?

All of the values being the same indicates that it's not, and you need to set up a relationship.

Are you able to share some sample data? This would be much easier to solve if I can work on something.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[pbiforum_123]

@mark_endicott Yes HsCode belongs to the same table. I am not able to share the onedrive link due to security reason.

[mark_endicott]

@pbiforum_123 then just paste a small subsection of your data table into a message. I will be able to recreate it in my own file. 

Something like the below will work:

HsCode	CustomsValue	EntryIdentifier	SupplementaryUnitQty

[pbiforum_123]

CBAM_Goods_InScope =

DATATABLE(

    "EntryIdentifier", STRING,

    "Product", STRING,

    "CustomsValue", INTEGER,

    {

        {"E1", "P1", 100},

        {"E1", "P2", 200},

        {"E2", "P3", 300},

        {"E3", "P4", 50},

        {"E3", "P5", 30},

        {"E4", "P6", 700}

    }

)

Filtered_Customs_Data_Input =

VAR Threshold =

    SELECTEDVALUE(ThresholdParameter[Value], 0)  -- scalar fallback

VAR CustomsWithTotal =

    ADDCOLUMNS(

        SUMMARIZE('CBAM_Goods_InScope', [EntryIdentifier]),

        "TotalCustomsValue", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))

    )

VAR FilteredIdentifiers =

    FILTER(CustomsWithTotal, [TotalCustomsValue] > Threshold)

RETURN

    FILTER(

        'CBAM_Goods_InScope',

        'CBAM_Goods_InScope'[EntryIdentifier] IN

            SELECTCOLUMNS(FilteredIdentifiers, "EntryIdentifier", [EntryIdentifier])

    )

ThresholdParameter =

DATATABLE("Value", INTEGER, {{0}, {100}, {200}, {300}, {400}, {500}, {1000}})

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)

[mark_endicott]

@pbiforum_123 - 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!