topic Re: DAX - how to filterout rows based on two (or more) identical columns in DAX Commands and Tips

DAX - how to filterout rows based on two (or more) identical columns

HCK — Fri, 30 Sep 2022 09:24:07 GMT

Hello,

I am relativelly new to DAX and I'm trying to figure out filtering of duplicate values. I have data (Qty) for more materials (PN=Part numbers) for different dates. there can be more items for one PN and one date. I need to calculate day by day how many items per PN are available - running total.
On top of that I need to recalculate that to a different unit imagine a box. Each PN can fit different qty to a box.
Lastly every PN belongs to a family and the aim is to know how many boxes will be occupied per family each day.

Movements:

Planned_date	End date	PN	Index	Data1	Datax	Qty	M1	M2=M1/UM
28.09.2022	03.10.2022	A	1			10	10	2
28.09.2022	29.09.2022	B	7			100	100	50
30.09.2022	04.10.2022	B	8			6	106	53
04.10.2022	05.10.2022	A	2			20	30	6
05.10.2022	05.10.2022	A	3			-5	25	5
05.10.2022	05.10.2022	A	4			-3	22	4,4
05.10.2022	05.10.2022	A	5			7	15	3
05.10.2022	09.10.2022	B	9			-50	56	28
06.10.2022	31.12.2024	A	6			10	25	5
10.10.2022	31.12.2024	B	10			16	72	36

Units	UM
A	5
B	2

Family
A	Family 1
B	Family 2
C	Family 1
D	Family 3

Target:

M3	28.09.2022	29.09.2022	30.09.2022	01.10.2022	02.10.2022	03.10.2022	04.10.2022	05.10.2022	06.10.2022	07.10.2022	08.10.2022	09.10.2022	10.10.2022	11.10.2022
A	2	2	2	2	2	2	6	3	5	5	5	5	5	5
B	50	50	53	53	53	53	53	28	28	28	28	28	36	36
Total	52	52	55	55	55	55	59	31	33	33	33	33	41	41

I succesfully created a calculated column M1:

M1 =

var CurrentDate = Movements[Planned_Date]

var CurrentPN= Movements[PN]

var CurrentIndex = Movements[Index]

var Filteredtable = FILTER(Movements, Movements[Planned_Date]<=CurrentDate && Movements[PN]=CurrentPN && Movements[Index]<=CurrentIndex)

Return

calculate(SUM(Movements[Qty]), Filteredtable)

as well as measure M2

M2 = Calculate(SUMX(Movements,Movements[M1]/Related(Units[UM])))

but I am struggling with M3 with the duplicate lines (Index 3 & 4)

M3 =

var Valbydate =CALCULATE (

MAXX(

Filter(

CROSSJOIN(Movements,'Date'),

'Date'[Date] >= 'Movements'[Planned_Date] && 'Date'[Date]<= 'Movements'[EndDate]

Movements[Running total]

)

Return

if(isblank(Valbydate),0,Valbydate)
If I use Maxx it works oK on PN level, but of course wrong on Family level.
I guess to use SUMX but before that I would need to somehow filter out "duplicate" lines with index 3 & 4. I suppose somehow with DISTINCT or SUMMARIZE but can't guess how.

As my running total M1 is based on DAX, I am looking for a DAX sollution.

I would appreciate any advice a lot.
Thank you in advance.

Re: DAX - how to filterout rows based on two (or more) identical columns

Anonymous — Mon, 03 Oct 2022 05:35:29 GMT

Hi HCK ,

I created a sample pbix file(see attachment), please check if that is what you want.

If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: DAX - how to filterout rows based on two (or more) identical columns

HCK — Mon, 03 Oct 2022 14:47:47 GMT

Thank you very much for your advice! You should me the logic to solve my issue. It seems that with a little adjustments of the columns and the measure I get to the required results! I want to verify on more data tomorrow and Accept your advice as a solution.

Thank you a lot!

Re: DAX - how to filterout rows based on two (or more) identical columns

HCK — Wed, 05 Oct 2022 07:01:15 GMT

So Finally I get to my sollution. The major breakthrough was your setup of the New Index column.

That was basically my initial wish:

"how to filterout rows based on two (or more) identical columns"

Answer: using your New index and then fitering the table by: NOT isblank

Details: I used the New Index to filterout my table using:

Reduced = Calculatetable(

Movements, Filter(Movements, NOT ISBLANK(Movements[New Index])

))
And on that table I performed the Measure:

M4 =

VAR _seldate =

SELECTEDVALUE ( 'Date'[Date] )

VAR _selunits =

SELECTEDVALUE ( 'Units'[Units] )

RETURN

CALCULATE (

SUM ( Reduced[M2]),

FILTER (

Reduced,

Reduced[Planned_Date] <= _seldate

&& Reduced[EndDate] >= _seldate

&& Reduced[Index]=Reduced[New Index]))
And by that I got my wanted results.
Thank you very much!