Custom column or measure

[slothman]

Hello all,

I need help with a custom column or maybe a measure with the following logic:

Basically I have a table with categories and some values around them (Date column) where not all rows have data for that value column. You can think of category column as a hierarchy. What I want to achieve is to remove the rows that do not have any data but keep the parent nodes associated with the rows that have data. 

The logic for OutcomeWanted (OW) column/measure:

• Check if Date column has a value, if it does then OW value is 1;
• If OW = 1 assign 1 for it's immediate parents:
  • On row 3 below since the data appears on depth 2 its parent would be depth = 1 with MAX(SortOrder) since there could be other step-parent categories in that level with or without their own children. 
  • Once the parent is assigned iterate up and find parent's parent and evaluate to 1.
  • To better illustrate the logic, consider row with Child_2_3_2_1. 
    • It has data thus would evaluate to 1.
    • Since it is on depth 4 its parent would be with depth 3.
    • Since there are multiple categories with depth 3, the one with closest SortOrder should be assigned as its parent. So Child_2_3_2_1 has a SortOrder of 12, thus its parent must be row with Depth = 3 and Max(SortOrder) < CurrentChild(SortOrder), which is Child_2_3_2
    • Once Child_2_3_2 value is found its parent must be found with the same logic and assigned a value of 1. In this case it would be - Child_2_3.
    • So on and so forth untill the Parent category. 
• If Date column is empty then assign 0.

Category	Date	SortOrder	Depth	OutcomeWanted
Parent		1	0	1
Child_1		2	1	1
Child_1_1	1/1/2020	3	2	1
Child_1_2		4	2	0
Child_1_2_1		5	3	0
Child_2		6	1	1
Child_2_1		7	2	0
Child_2_2		8	2	0
Child_2_3		9	2	1
Child_2_3_1		10	3	0
Child_2_3_2		11	3	1
Child_2_3_2_1	2/2/2020	12	4	1
Child_3		13	1	0

The idea is to filter out based on OW column by showing only values with 1. So my final table would look like this:

Category	Date	SortOrder	Depth	OutcomeWanted
Parent		1	0	1
Child_1		2	1	1
Child_1_1	1/1/2020	3	2	1
Child_2		6	1	1
Child_2_3		9	2	1
Child_2_3_2		11	3	1
Child_2_3_2_1	2/2/2020	12	4	1

Thanks for looking.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if the below solution is the best way to approach, but please check the below picture and the attached pbix file. I tried to use PATH function and PATHITEM function to create the Outcome Wanted Calculated Column.

[slothman]

Thank you very much, this works great!