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
        )
Justas4478's avatar
Justas4478
Post Prodigy
1 year ago

FBergamaschi These are all the values in [Physical Incentory Procedure]:
AL
AS
HL
HS
And in [Reason]:
CCIV
LSPI
PTPI
STND
UNAS
UPLD

Do I have to list them all in the dax to make sure that it works?

FBergamaschi's avatar
FBergamaschi
Super User
1 year ago

That depends on what you want to impose on those columns?

 

You want to allow only some values (which?) or you wanto to remove the filters form thos columns? Please explain and I shall provide the code

 

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI