Forum Discussion
ALL function with text
Hi,
I have this dax measure:
I am trying to add more variables in to ALL function, but get this error when it tries to run.
This is what I was hoping would work.
The [Physical Inventory Procedure] and [Reason] are columns that I tried to add and they are rejected since they are text.
Is there anything that can be changed to make it work?
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 slicerALL('Stock adjustment data'))VAR CurrentProduct =SELECTEDVALUE('Product information'[Product])VAR ProductTotal =CALCULATE(MAXX(FILTER(SummaryTable, [Product] = CurrentProduct),[TotalDiff]))RETURNIF(ISINSCOPE('Product information'[Product]),IF(ProductTotal < 0,CALCULATE(MINX(FILTER(SummaryTable, [Product] = CurrentProduct),[TotalDiff])),ProductTotal),SUMX(SummaryTable, [TotalDiff]) // ⬅️ Subtotal logic)
11 Replies
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
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?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.
Hi Justas4478
Can you please try the belwo dax ?
--- > All( ) does not support multiple columns from different tables.
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.
Hi Justas4478
Please try this:
ALCULATE(
SUM('Stock adjustment data'[Difference Quantity(PIC)]),
ALL('Calendar'[Date]),
ALL('Stock adjustment data'[Physical Inventory Procedure]),
ALL('Stock adjustment data'[Reason])
)
I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!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!
