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!

     

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
1 year ago

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!

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

    Hi Anonymous,

    Have you tried this solution?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

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