Solved: Removing Nulls When Using Values Show On Rows

millwardt · ‎04-28-2020

I've created a matrix using the Show On Rows option for the values, and as the value is not applicable for every grouping there are a large number of rows where the data is blank.

Is there any way to suppress these?

(Unfortunately setting a filter of 'is not blank' causes groupings which do not have a value for this measure to disappear completely, although they have measures on other rows)

parry2k · ‎04-28-2020

@millwardt it seems like data transformation problem, your table is not optimized to view in the matrix, can you share how your raw data table looks like, and I also mentioned if you are using any measure on matrix, share what that is?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k · ‎04-28-2020

@millwardt can you share the screenshot to explain it further and also what measure you are using for matrix, share dax expression.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

millwardt · ‎04-28-2020

Please see a mockup below

Column B are the 'Values' selected in the matrix, which are set to Show On Rows

If i filter Charge Type A to be 'is not blank' then Customer B disappears completely, rather than just the Customer B, Charge Type A row disappearing.

asdfas

parry2k · ‎04-28-2020

@millwardt it seems like data transformation problem, your table is not optimized to view in the matrix, can you share how your raw data table looks like, and I also mentioned if you are using any measure on matrix, share what that is?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

millwardt · ‎04-28-2020

I thought that may be the case! It is based on a DirectQuery source so i may look at implementing the UNPIVOT in the Oracle source database before presenting the data to PowerBI

Thank you for your help