Parameter in summarize

[pbiforum_123]

Value which is in the Threshold variable is not getting passed. Kindly help me on this...

Filtered_Customs_Data_Input =

VAR Threshold = [Parameter Value]--500  -- Replace with your parameter or use a disconnected table parameter

VAR FilteredIdentifiers =

    SUMMARIZE(

        FILTER(

            ADDCOLUMNS(

                SUMMARIZE('CBAM_Goods_InScope', [EntryIdentifier]),

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

            ),

            [TotalCustomsValue] > Threshold

        ),

        [EntryIdentifier]

    )

RETURN

    FILTER(

        'CBAM_Goods_InScope',

        'CBAM_Goods_InScope'[EntryIdentifier] IN SELECTCOLUMNS(FilteredIdentifiers, "EntryIdentifier", [EntryIdentifier])

    )

[v-pagayam-msft · Accepted solution]

Hi @pbiforum_123 ,
Thank you for reaching out to us on Microsoft Fabric Community Forum. Also  thanks @Nasif_Azam and @rajendraongole1 for the helpful insights!

I tried to recreate it on my local upon my understanding.Please refer the screenshot and file for your reference.

If this answer meets your requirement,give us kudos and consider accepting it as solution.If still require further assistance, feel free to reachout!

Regards,
Pallavi G.

[pbiforum_123]

@v-pagayam-msft Thanks a lot for your time and help. Based on the value that you have selected in the Threshold I wanted to filter the records in the Filtered_Customs_Data_Input table so that I dont need to use the visual filter at all. Is that feasible?

Basically based the below filter condition, only those records which matches the given condition only should be fetched in Filtered_Customs_Data_Input table

VAR FilteredIdentifiers =

    FILTER(CustomsWithTotal, [TotalCustomsValue] > Threshold)

 If you hardcode this value it works in similar way it should work based on the parameter.

VAR FilteredIdentifiers =

    FILTER(CustomsWithTotal, [TotalCustomsValue] > Threshold) -- This is not working

VAR FilteredIdentifiers =

    FILTER(CustomsWithTotal, [TotalCustomsValue] > 150) -- This is working

[v-pagayam-msft]

Hi @pbiforum_123 ,
Thank you again for the follow-up. I would be happy to assist you!

Hardcoding a value like > 150 works because calculated tables are created at model load. But when using a dynamic value from a slicer (like Threshold), the table does not update. That is expected because calculated tables are not dynamic in the same way visuals or measures are, since calculated tables do not respond to slicers , only DAX measures do. For dynamic behavior, measures with visual-level filters are the way to go.

To get dynamic filtering based on a slicer, the best approach is to use the original CBAM_Goods_InScope table in your visuals. Then, create a measure like ShowRow that checks if each entry meets the selected threshold. Finally, apply a visual-level filter where ShowRow = 1, this gives you the same result you wanted from the calculated table, but in a way that responds to slicer changes.

I hope this helps.

Thank you.

[pbiforum_123]

@v-pagayam-msft Thanks for your valuable time and for your response! As you mentioned since Calculated table cannot be dynamic. Then in that case we can use Direct Query to filter the records right but disadvantages would be we there may be some DAX function which may not be supported in the DAX query.

If we are not going ahead with the Direct Query then applying the visual-level filter using ShowRow =1 will be the better option. Is that understand correct?

Since Calculated table cannot be dynamic then in that case can go ahead and remove these of lines of code right? Since it of no use now..

Basically this is the result set expeted in the table....

SELECT *
FROM [dbo].[Customs_Data_Input]
WHERE [EntryIdentifier] IN (
SELECT [EntryIdentifier]
FROM [dbo].[Customs_Data_Input]
GROUP BY [EntryIdentifier]
HAVING SUM([CustomsValue]) > Parameter
)

I will check this from my end and will mark this answer as correct. Please give me sometime...

[v-pagayam-msft]

Hi @pbiforum_123 ,
Yes. Using DirectQuery could enable you to filter directly at the source, but there are indeed limitations. So using a measure with a visual filter (like ShowRow) is the best way in your scenario.
If you need any further help while you are verifying the solution, please feel free to reach back .We are happy to support you!

Thank you.

[pbiforum_123]

@v-pagayam-msft Sure, will checking it out.. One question here...

SELECT *
FROM [dbo].[Customs_Data_Input]
WHERE [EntryIdentifier] IN (
SELECT [EntryIdentifier]
FROM [dbo].[Customs_Data_Input]
GROUP BY [EntryIdentifier]
HAVING SUM([CustomsValue]) > Parameter
)

The group by which I am doing it in the inner subquery may I know where it is happening? 

Is it happening here? Please clarify...

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)

One more question, though it was not part of the requirement which I mentioned before. 

If we have to group by one more column aggregate different column(not same as CustomsValue) on top of the above result then I below I cannot go ahead with the above filter solution that you shared.  

Basically I want to achieve this.. The one in BOLD is already taken care using visual filter but I dont think can achieve the same thing using filter now. WE need to find different alternate now.

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]) > 500
)
GROUP BY
HsCode;

[pbiforum_123]

@v-pagayam-msft Did you get chance to check once?

[v-pagayam-msft]

Hi @pbiforum_123 ,

Yes ,grouping by EntryIdentifier happens within your measure when we use CALCULATE(SUM(...), ALLEXCEPT()). And for your follow-up, you are right that with this filter solution, you are limited to filtering by the sum per entry and then retaining those IDs in your visuals. If you want to group by another column and sum a different field after that, you would need to create a new table or a new measure to account for that.
Hope this helps.

Thank you.

[pbiforum_123]

@v-pagayam-msft Yes I tried to do this within a same table to achieve below but I couldn't do it. I think creating different table may not solve the purpost right? 1st group by based on the parameter and on top of the 2nd group by. Not sure how to achieve this..

Basically below is the output which I am expecting... We have already done the 1st of part.. but doing the 2nd part on top of the 1st part I am not getting how to do it...

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]) > 500
)

[v-pagayam-msft]

Hi @pbiforum_123 ,

I'm glad the original question around dynamic filtering using the threshold slicer is resolved! 

You are now moving into a more advanced scenario involving grouped summaries with conditional filtering  which is a different use case.

To help keep this thread focused and useful for others with similar issues, I kindly suggest creating a new post for this next requirement. We will be happy to assist you further!

Thank you for your understanding!

[pbiforum_123]

@v-pagayam-msft I have closed this thread as you have mentioned. I have created the new thread for the next part of requirement as you have mentioned. So can you please help me out for the same?

Grouped summaries with conditional filtering - Microsoft Fabric Community

[pbiforum_123]

@v-pagayam-msft Can you please help me out here with some solution or alternate other than DirectQuery since we would want to achieve this using import mode for its flexibility. 

[pbiforum_123]

May be I cannot use Parameter/Variable with Summarize? If yes, then how can i achieve this? Basically I want to filter out the records that are in scope for calculation.

[Nasif_Azam]

Hey @pbiforum_123 ,

The issue you're encountering is "Value which is in the Threshold variable is not getting passed". The value in the Threshold variable is not getting passed properly in your SUMMARIZE logic is often due to context transition or the way Threshold is referenced within the nested row context of FILTER and ADDCOLUMNS.

Root Cause

The variable Threshold is a scalar defined outside of the FILTER expression, but inside the ADDCOLUMNS, you're operating within a row context introduced by SUMMARIZE, which does not automatically convert into a filter context. As a result, DAX may not correctly evaluate Threshold for each row especially when used in CALCULATE inside ADDCOLUMNS.

Use VAR and restructure your code like this:

Filtered_Customs_Data_Input =
VAR Threshold = [Parameter Value] -- e.g., 500
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])
    )

 

Alternative: Use a Measure Instead of Variable

If [Parameter Value] is dynamic (like a disconnected slicer table), consider creating a measure and referencing it instead:

Threshold Measure = SELECTEDVALUE('Parameter Table'[Value])

And then:

VAR Threshold = [Threshold Measure]

 

Things to remember

• Always compute columns before filtering when dealing with nested contexts.

• Use SELECTCOLUMNS or VALUES to extract clean lists for use in IN clauses.

• Ensure Threshold is a scalar value — not a table or column.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[pbiforum_123]

this is not working as well...

[Nasif_Azam]

Thanks for the update. If the revised approach didn’t solve the issue, let’s take a deeper look at possible reasons and other workarounds:

Potential Issues

1. [Parameter Value] might not be evaluated as expected

   • If [Parameter Value] is coming from a slicer or a disconnected table, it must be a scalar. Use SELECTEDVALUE() to ensure it returns a single value:

     VAR Threshold = SELECTEDVALUE('Parameter Table'[Value], 500)  // fallback to 500

2. Threshold might be blank

   • If no value is selected in the parameter slicer, [Parameter Value] may return blank, causing all rows to be filtered out. You can guard against this with:

     VAR Threshold = IF(ISBLANK(SELECTEDVALUE('Parameter Table'[Value])), 0, SELECTEDVALUE('Parameter Table'[Value]))

3. Data Type Mismatch

   • Double-check if CustomsValue and the parameter are both numeric. If one is text and the other is numeric, the comparison may silently fail.

Try this full version with explicit fallback and clean context flow:

Filtered_Customs_Data_Input =
VAR Threshold = SELECTEDVALUE('Parameter Table'[Value], 0)  // default to 0 if nothing selected

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

 

Debug Tip

Temporarily add a measure like this to check what your parameter is returning:

Measure_CheckThreshold = SELECTEDVALUE('Parameter Table'[Value])

Add it to a card visual to verify it’s not blank or unexpected.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[pbiforum_123]

@Nasif_Azam  Thanks a lot for your quick response. Not sure if your response is generated by Chat GPT but it is not giving expected output.

[Nasif_Azam]

A small portion of my response was generated by Chat GPT because your question was not very clear and to provide a correct answer you have to mention the data modeling as well as sclicer and matrix or any visuals screenshot you have your current report. Without those information, it was hard to provide or understand the problem you face.

Best Regards,
Nasif Azam

[pbiforum_123]

@Nasif_Azam It has confidential data hence cannot share it. Sorry for not being clear. 

@v-pagayam-msft Has shared with some sample data you may if you can help me with that.

[rajendraongole1]

Hi @pbiforum_123  - create a measure for selected value as below:

SelectedThreshold = SELECTEDVALUE(ThresholdParameter[Value], 500)

Now , create a measure now:

Filtered_Customs_Data_Input =
VAR Threshold = [SelectedThreshold] -- this should be a scalar measure
VAR FilteredIdentifiers =
SUMMARIZE(
FILTER(
ADDCOLUMNS(
SUMMARIZE('CBAM_Goods_InScope', [EntryIdentifier]),
"TotalCustomsValue", CALCULATE(SUM('CBAM_Goods_InScope'[CustomsValue]))
),
[TotalCustomsValue] > Threshold
),
[EntryIdentifier]
)
RETURN
FILTER(
'CBAM_Goods_InScope',
'CBAM_Goods_InScope'[EntryIdentifier] IN SELECTCOLUMNS(FilteredIdentifiers, "EntryIdentifier", [EntryIdentifier])
)

Hope this helps.