Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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  S...
Show and Tell
Tips and Tricks
  • mark_endicott's avatar
    mark_endicott
    1 year ago

    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!

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
mark_endicott
Icon for Super User rankSuper User
1 year ago

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

    Anonymous 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
           
           
           
           

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago
    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's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

    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!

     

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

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