topic Re: Inherit Values from previous rows in DAX Commands and Tips

Inherit Values from previous rows

BenediktW — Wed, 10 Jan 2024 13:05:14 GMT

Dear Community,

I am facing an issue regarding an inheriting logic. I have a table in my canvas environment (example attached with the key columns), which contains certain subcategories. Those are having an descending index, which will start new for every subcategory. Additionally, I have one value column, which includes for every first row of an subcategory (highest index) an 0 or 1 value.

With the calculation I want to fill in a new column for every row in the respective subcategory this "first row value".

One special case is, that in the column inherit flag the indentificator "MSCOP" can occur, which turns over the inherting value, as it was previously 0 then its now 1 and the other way round.

Summarization: I want to have an inheriting logic of values (0 or 1), which is firstly just inheriting the value from the previous row. When the indetificators "First row" or "MSCOP" occur, a re-evalution of the to-inherit value should happen.

I tried with some code attached below, but wasnt successfull so far.

Thank you very much

VAR CurrentSubcategory = MyTable[Subcategory] VAR CurrentValue = MyTable[First Row Value] VAR InheritFlag = MyTable[Inherit_Flag] VAR CurrentRow = MyTable[Index.1] VAR InheritedValue = CALCULATE( MAXX( FILTER( ALL(MyTable), MyTable[Subcategory]] = CurrentSubcategory && MyTable[First Row Value] <> BLANK() && MyTable[Index.1] < CurrentRow ), MyTable[First Row Value] ), ALLEXCEPT(MyTablet, MyTable[Subcategory]]) ) VAR NewSubcategory = NOT ( MyTable[Subcategory]] = CALCULATE( VALUES(MyTable[Subcategory]]), MyTable[Index.1] = CurrentRow ) ) RETURN IF( OR( ISBLANK(CurrentValue), InheritFlag = "MSCOP" ) || NewSubcategory, InheritedValue + IF(InheritFlag = "MSCOP", -1, 0), BLANK() )

Subcategory column	Index.1	First Row value	Inherit_Flag	Wished Result
Subkategory 1	5	0	First Row	0
Subkategory 1	4			0
Subkategory 1	3		MSCOP	1
Subkategory 1	2			1
Subkategory 1	1			1
Subkategory 2	5	1	First Row	1
Subkategory 2	4			1
Subkategory 2	3			1
Subkategory 2	2			1
Subkategory 2	1			1
Subkategory 3	5	0	First Row	0
Subkategory 3	4			0
Subkategory 3	3		MSCOP	1
Subkategory 3	2			1
Subkategory 3	1			1

Re: Inherit Values from previous rows

barritown — Wed, 10 Jan 2024 14:55:55 GMT

Hi BenediktW,

If "MSCOP" can appear only once, you can try such a calculated column:

In plain text:

Result = VAR CurrentSubcategory = [Subcategory column] VAR CurrentIndex = [Index.1] VAR InitialValue = MINX ( FILTER ( MyTable, [Subcategory column] = CurrentSubcategory ), [First Row value] ) VAR FinalValue = ABS ( COUNTROWS ( FILTER ( MyTable, [Subcategory column] = CurrentSubcategory && [Index.1] >= CurrentIndex && [Inherit_Flag] = "MSCOP" ) ) - InitialValue ) RETURN FinalValue

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Inherit Values from previous rows

BenediktW — Wed, 10 Jan 2024 15:03:17 GMT

Hi,

thank you very much already! That is working better than my approaches so far. Unfortunately, it can happen that the "MSCOP" Identifikator shows up multiple times per Subkategory.

Re: Inherit Values from previous rows

barritown — Wed, 10 Jan 2024 15:12:36 GMT

Okay, try this modification then:

Result = VAR CurrentSubcategory = [Subcategory column] VAR CurrentIndex = [Index.1] VAR InitialValue = MINX ( FILTER ( MyTable, [Subcategory column] = CurrentSubcategory ), [First Row value] ) VAR FinalValue = ABS ( MOD ( COUNTROWS ( FILTER ( MyTable, [Subcategory column] = CurrentSubcategory && [Index.1] >= CurrentIndex && [Inherit_Flag] = "MSCOP" ) ), 2 ) - InitialValue ) RETURN FinalValue

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Inherit Values from previous rows

BenediktW — Wed, 10 Jan 2024 15:19:40 GMT

Works well, but when the first row value is 1 and there is an "MSCOP" Identificator. The following rows after that are staying one.

Re: Inherit Values from previous rows

barritown — Wed, 10 Jan 2024 18:56:07 GMT

"Subkategory 4" on the screenshot below sounds like the case you described, but it shows the expected values.

Can you share a sequence where it breaks?

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian