Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

ALL function with text

Hi, I have this dax measure: CALCULATE(SUM('Stock adjustment data'[Difference Quantity(PIC)]),ALL('Calendar'[Date])) I am trying to add more variables in to ALL function, but get this error when i...
Need Help
  • Justas4478's avatar
    Justas4478
    1 year ago

    FBergamaschi This is solution provided by copilot. It seams to work.
    I dont know if there is need to siplify it but here it is.

    VAR SummaryTable =
        CALCULATETABLE(
            SUMMARIZE(
                FILTER(
                    'Stock adjustment data',
                    'Stock adjustment data'[Physical Inventory Status] = "POST"
                ),
                'Product information'[Product],
                "TotalDiff", SUM('Stock adjustment data'[Difference Quantity(PIC)])
            ),
            REMOVEFILTERS('Calendar'[Date]), // ⬅️ This removes the impact of the date slicer
            ALL('Stock adjustment data')
        )

    VAR CurrentProduct =
        SELECTEDVALUE('Product information'[Product])

    VAR ProductTotal =
        CALCULATE(
            MAXX(
                FILTER(SummaryTable, [Product] = CurrentProduct),
                [TotalDiff]
            )
        )

    RETURN
        IF(
            ISINSCOPE('Product information'[Product]),
            IF(
                ProductTotal < 0,
                CALCULATE(
                    MINX(
                        FILTER(SummaryTable, [Product] = CurrentProduct),
                        [TotalDiff]
                    )
                ),
                ProductTotal
            ),
            SUMX(SummaryTable, [TotalDiff]) // ⬅️ Subtotal logic
        )
v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

Hi Justas4478,

Great to hear it’s working well for you!

 

Your approach of using SUMMARIZE with a filter for "POST" status and grouping by product is effective. This ensures you get accurate totals per product, even with visual filters applied. Including logic to check for negative values is also a good move.

summarizing per product number is a good approach if you're aiming to get a consistent result per product. It helps avoid row-level filter effects and gives you better control over how totals are displayed.

Thank you again, and we hope you continue to find the Microsoft Fabric Community helpful!