Matrix Visual Should show one row rather than summing next levels

[mysasai]

Hi all,

I have a situation and I'm assuming it is not possible with straight forward functionalities of PBI. 

Please help me in solving this. 

I have a data source, something like below. 

Group	LEVEL1	LEVEL2	LEVEL3	LEVEL4	Value
XYZ	Global				8
XYZ	Global	APAC			8
XYZ	Global	APAC	ENT		3
XYZ	Global	APAC	ENT	IHE	0
XYZ	Global	APAC	ENT	M&E1	3
XYZ	Global	APAC	ENT	M&E2	0
XYZ	Global	APAC	ENT	M&E3	6
XYZ	Global	APAC	IMS		14
XYZ	Global	APAC	IMS	IMS1	14
XYZ	Global	APAC	IMS	IMS2	20
XYZ	Global	APAC	IMS	IMS3	0
XYZ	Global	APAC	IMS	IMS4	6
XYZ	Global	APAC	MED		0
XYZ	Global	APAC	MED	ADV	0
XYZ	Global	APAC	MED	BCS	0
XYZ	Global	APAC	MED	CAS	0
XYZ	Global	APAC	MED	PUB	0
XYZ	Global	NA			0
XYZ	Global	NA	DTS		0
XYZ	Global	NA	DTS	ITS	0
XYZ	Global	NA	WTS		0
XYZ	Global	NA	WTS	WTS2	0
ABC	Global				4
ABC	Global	APAC			4
ABC	Global	APAC	ENT		2
ABC	Global	APAC	ENT	IHE	0
ABC	Global	APAC	ENT	M&E1	2
ABC	Global	APAC	ENT	M&E2	0
ABC	Global	APAC	ENT	M&E3	3
ABC	Global	APAC	IMS		7
ABC	Global	APAC	IMS	IMS1	7
ABC	Global	APAC	IMS	IMS2	10
ABC	Global	APAC	IMS	IMS3	0
ABC	Global	APAC	IMS	IMS4	3
ABC	Global	APAC	MED		0
ABC	Global	APAC	MED	ADV	0
ABC	Global	APAC	MED	BCS	0
ABC	Global	APAC	MED	CAS	0
ABC	Global	APAC	MED	PUB	0
ABC	Global	NA			0
ABC	Global	NA	DTS		0
ABC	Global	NA	DTS	ITS	0
ABC	Global	NA	WTS		0
ABC	Global	NA	WTS	WTS2	0

I have dragged them into Matrix Visual like this.

Currently it shows like the screenshot below. 

I don't want to show the blank rows coming up in between and also don't want to show totals summed. 

I would want to see like below.

Thanks in advance for helping. 

@amitchandak @Greg_Deckler @Icey 

[Selva-Salimi · Accepted solution]

Hi @mysasai 

you can write a measure as follows:

Measure 2 = if (SELECTEDVALUE(My_Table[LEVEL4]) <> blank () , max(My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL3]) <> BLANK() , maxx(filter(My_Table,My_Table[LEVEL4]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL2]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL3]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL1]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL2]=blank()) , My_Table[Value]) ,blank()))))

and add it to values of your matrix.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

[Selva-Salimi · Accepted solution]

Hi @mysasai 

you can write a measure as follows:

Measure 2 = if (SELECTEDVALUE(My_Table[LEVEL4]) <> blank () , max(My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL3]) <> BLANK() , maxx(filter(My_Table,My_Table[LEVEL4]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL2]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL3]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL1]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL2]=blank()) , My_Table[Value]) ,blank()))))

and add it to values of your matrix.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

[mysasai]

Thank you @Selva-Salimi, this worked and values are not suming up.

Any idea how would I hide blank rows highlighted in Red lines in screenshot. 

[Selva-Salimi · Accepted solution]

@mysasai 

then it would be better to update your measure as follows: 

Measure= if (ISINSCOPE(My_Table[LEVEL4]) , max(My_Table[Value]), if (ISINSCOPE(My_Table[LEVEL3]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2]=SELECTEDVALUE(My_Table[LEVEL2]) && My_Table[LEVEL3]=SELECTEDVALUE(My_Table[LEVEL3]) && My_Table[LEVEL4] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[LEVEL2]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2]=SELECTEDVALUE(My_Table[LEVEL2]) && My_Table[LEVEL3] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[LEVEL1]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[Group]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) &&  My_Table[LEVEL1] = blank()),My_Table[Value]))))))

and easily can filter blank in the filter pane of matrix visual as follows"

do the same for each level. if any Q feel free to ask.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

[mysasai]

Hi @Selva-Salimi ,

I implemented the solution on a large dataset, adding a few other filter conditions such as ID and Date. However, after importing a few months of data, I encountered a resource error. It appears that the measure is comparing each row with the entire imported dataset, leading to performance issues.

I also tried using Direct Query. Although I only need to process 50,000 rows, the operation is comparing against millions of records in the database, resulting in the error: "Error fetching data for this visual in Power BI: 1,000,000 row limit error."

Is there a way to optimize the measure to first filter by a specific ID and then perform the remaining DAX operations on the filtered dataset?

Thank you for your assistance.