Forum Discussion
Parameter in summarize
Hi Anonymous ,
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.
Hi Anonymous ,
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.
Anonymous 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...
Hi Anonymous ,
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.Anonymous 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]))RETURNIF(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;Anonymous Did you get chance to check once?