topic Re: Helo with Dax formula for calculating requirement Independent from table column in DAX Commands and Tips

Helo with Dax formula for calculating requirement Independent from table column

ValeriaBreve — Mon, 11 Aug 2025 16:27:56 GMT

Hello,

I have a table as follows:

Date	ID & Text	Pack Type	# Requirements per Pallet	# Req CP3
8/11/2025	1	A	20
8/11/2025	2	A	10
8/11/2025	3	A	30
8/11/2025	4	B	70	10
8/12/2025	1	A	50
8/12/2025	2	A	90
8/12/2025	3	A	60
8/12/2025	4	B	250	50
8/13/2025	1	A	5
8/13/2025	2	A	90
8/13/2025	3	A	55
8/13/2025	4	B	200	50

# Req CP3 is what I need to calculate, and it is basically the total [# Requirements per Pallet] for Trade_Products[Pack Type]="B" for the selected date minus the sum of all [# Requirements per Pallet] for Trade_Products[Pack Type]="A" at the selected date.

I don't seem to be getting it right 😞

VAR SelectedDate = SELECTEDVALUE( Date_Table[Date] )

VAR PalletReq =
CALCULATE(
[# Requirements],
Trade_Products[ID & Text] = "4",
Date_Table[Date] = SelectedDate
)

//this should calculate the aggregate of all Product Types = "A" on the selected date

VAR FutsReq =
CALCULATE(
[# Requirements per Pallet],
Trade_Products[Pack Type] = "A",
Trade_Products[ID & Text] <> "4",
Date_Table[Date] = SelectedDate
)
RETURN
PalletReq - FutsReq

With this, I am getting a column that gives me the same numbers as per [# Requirements per Pallet], and not the aggregation I am looking for.

Can you help me understand which direction to take?

Thanks!

Kind regards

Valeria

Re: Helo with Dax formula for calculating requirement Independent from table column

bhanu_gautam — Mon, 11 Aug 2025 16:32:59 GMT

ValeriaBreve , Try using

DAX
VAR SelectedDate = SELECTEDVALUE(Date_Table[Date])

// Calculate the total requirements for Pack Type "B" on the selected date
VAR PalletReq =
CALCULATE(
SUM(Trade_Products[# Requirements per Pallet]),
Trade_Products[Pack Type] = "B",
Date_Table[Date] = SelectedDate
)

// Calculate the total requirements for Pack Type "A" on the selected date
VAR FutsReq =
CALCULATE(
SUM(Trade_Products[# Requirements per Pallet]),
Trade_Products[Pack Type] = "A",
Date_Table[Date] = SelectedDate
)

RETURN
PalletReq - FutsReq

Re: Helo with Dax formula for calculating requirement Independent from table column

Greg_Deckler — Mon, 11 Aug 2025 17:01:12 GMT

ValeriaBreve Try:

Measure = VAR __Type = MAX( 'Table'[Pack Type] ) VAR __Date = MAX( 'Table'[Date] ) VAR __ATable = FILTER( ALL( 'Table' ), [Date] = __Date && [Pack Type] = "A" ) VAR __Result = IF( __Type = "A", BLANK(), MAX( 'Table'[# Requirements per Pallet] ) - SUMX( __ATable, [# Requirements per Pallet] ) ) RETURN __Result

Re: Helo with Dax formula for calculating requirement Independent from table column

ValeriaBreve — Tue, 12 Aug 2025 06:43:49 GMT

Hi Greg_Deckler , thank you! however I have a date table for [Date] and another dimension table for [Pack Type" so when coming to Var _ATable I was not sure how to proceed as in this one everything is supposed to be on the same table (I have 2 dimensions and a fact table).

I ended up with something like the below, which works but it looks very complicated to me and I wonder whether I could have done it in a much simpler way....

VAR SelectedDate = SELECTEDVALUE( Date_Table[Date] )

VAR PalletReqTable =

SUMMARIZECOLUMNS(

Date_Table[Date],

Trade_Products[Pack Type],

FILTER( Trade_Products, Trade_Products[[ID & Text] = "4" ),

FILTER(

Date_Table,

Date_Table[Date] = SelectedDate

"Pallet Req", -[# Requirements]

)

VAR FutsReqTable =

SUMMARIZECOLUMNS(

Date_Table[Date],

Trade_Products[Pack Type],

FILTER(

ALL( Trade_Products ),

Trade_Products[Pack Type] = "A"

FILTER(

Date_Table,

Date_Table[Date] = SelectedDate

"Pallet Req", -[# Requirements per Pallet]

)

VAR UnionTable = UNION( FutsReqTable, PalletReqTable )

VAR FinalSummary =

GROUPBY(

UnionTable,

Date_Table[Date],

"Total Pallet Req", SUMX( CURRENTGROUP( ), [Pallet Req] )

)

RETURN

{

ROW("Max Total Pallet Req", MAXX(FinalSummary, [Total Pallet Req]))

}

Re: Helo with Dax formula for calculating requirement Independent from table column

ValeriaBreve — Tue, 12 Aug 2025 07:04:06 GMT

Hi bhanu_gautam thank you, however this measure is not aggregating by [ID & Text] so when I use it in the table it is returning the [# Requirements per Pallet] per row/[ID & Text]. I did find a solution (as per reply to Greg Deckler) but it si quite complicated - I wonder whether I could simplify it. Anyway, thank you for taking the time to look at this!

Re: Helo with Dax formula for calculating requirement Independent from table column

johnt75 — Tue, 12 Aug 2025 09:05:24 GMT

You can create a measure like

# Req CP3 = IF( SELECTEDVALUE( 'Table'[ID & Text] ) = 4, VAR PalletReq = CALCULATE( SUM( 'Table'[# Requirements per Pallet] ), REMOVEFILTERS( 'Table' ), 'Table'[ID & Text] = 4, VALUES( 'Date'[Date] ) ) VAR FutsReq = CALCULATE( SUM( 'Table'[# Requirements per Pallet] ), REMOVEFILTERS( 'Table' ), 'Table'[ID & Text] <> 4, 'Pack Type'[Pack Type] = "A", VALUES( 'Date'[Date] ) ) RETURN PalletReq - FutsReq )

See attached PBIX for reference.

Re: Helo with Dax formula for calculating requirement Independent from table column

Greg_Deckler — Tue, 12 Aug 2025 15:03:53 GMT

ValeriaBreve So that would be:

Measure = VAR __Type = MAX( 'PackDimension'[Pack Type] ) VAR __Date = MAX( 'DateDimension'[Date] ) VAR __ATable = FILTER( ALL( 'Table' ), [Date] = __Date && [Pack Type] = "A" ) VAR __Result = IF( __Type = "A", BLANK(), MAX( 'Table'[# Requirements per Pallet] ) - SUMX( __ATable, [# Requirements per Pallet] ) ) RETURN __Result

Re: Helo with Dax formula for calculating requirement Independent from table column

ValeriaBreve — Wed, 13 Aug 2025 06:22:44 GMT

Greg_Deckler Hi Greg, got it. I still have my 2 dimension tables - meaning that I cannot use the [Pack Type] as is in the _ATable because it does not exit in the fact table - fact and dimension are related through Material ID. So I modified it this way:
VAR __ATable =
FILTER(
ALL( PACKED_INV ),
PACKED_INV[START_DATE] = __Date
&& RELATED( Trade_Products[Pack Type] ) = "A"
)
and it works beautifully :-).

Thank you so much!

Re: Helo with Dax formula for calculating requirement Independent from table column

ValeriaBreve — Wed, 13 Aug 2025 06:33:19 GMT

johnt75 Hi John, your solution also works beautifully! A pity I cannot accept 2 solutions, I already accepted Greg's as it came first, but thank you so much - I have learned a lot from your code!