Forum Discussion
Matrix Visual Should show one row rather than summing next levels
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.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.
Solution Sage
then it would be better to update your measure as follows:
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.