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)

[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 · Accepted solution]

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

[mark_endicott]

@pbiforum_123 - This table does not have your HsCode or SupplementaryUnitQty values in? 

I thought you said it was all in the same table??

I can make some up, but I'm then making an assumption about how your data is structured, which will matter for the DAX Code. 

[pbiforum_123]

Yes HsCode will be there in the same table only...Sorry it was prepared based on the initial requirement..

[mark_endicott]

@pbiforum_123 - then please review my last post. I have attached a file to show my DAX working. 

[pbiforum_123]

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