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
        )
FBergamaschi's avatar
FBergamaschi
Super User
1 year ago

You need to specify the codition you want those column to fulfill, for example:

 

CALCULATE(

                SUM('Stock adjustment data'[Difference Quantity(PIC)]),

               ALL('Calendar'[Date]),

               'Stock adjustment data'[Physical Inventory Procedure] ="Value1",
               'Stock adjustment data'[Reason]="Value2"
)

 

or some other criteria (pls clarify so I can help)

 

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

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?

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi Justas4478 

    No need to add all the values.

    CALCULATE(
    SUM('Stock adjustment data'[Difference Quantity(PIC)]),
    ALL('Calendar'[Date]),
    ALL('Stock adjustment data'[Physical Inventory Procedure]),
    ALL('Stock adjustment data'[Reason])
    )

    If this answers your questions, kindly accept it as a solution and give kudos.

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      1 year ago

      mdaatifraza5556 I tired it but it still returns different results when you filter.
      The value circled in red is what I am hoping would be always shown.


      And I would hope it would be same result in all rows in that column

       

      • FBergamaschi's avatar
        FBergamaschi
        Super User
        1 year ago

        CALCULATE(

                        SUM('Stock adjustment data'[Difference Quantity(PIC)]),

                       REMOVEFILTERS('Calendar'[Date]),

                       REMOVEFILTERS('Stock adjustment data')
        )

        in this eay you remove all the filters, let's see if that is OK or you need to preserve some and we will fix it

         

        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

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      1 year ago

      mdaatifraza5556 I was thinking, maybe it needs to be summarized per product number and not sum to get outcom I am aiming for

  • 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