Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
tolsen37
Regular Visitor

Power BI Matrix, show only rows that have values in all columns

Hello,

 

I am creating a Matrix for a cross-sell analysis, and am looking to only show rows where data is populated for each column.

The below picture shows the current Matrix. I would want to create something where only customers that have revenue for each selected brand are shown.

tolsen37_0-1698856749935.png

Here are the current fields used to build the Matrix

tolsen37_1-1698856821214.png

 

Any ideas?

1 ACCEPTED SOLUTION
tolsen37
Regular Visitor

I was able to solve the issue by using the below DAX:

 

Calculation = IF (
CALCULATE(DISTINCTCOUNT(Table[Brand]), ALLSELECTED(Table[Brand]))
= COUNTROWS(ALLSELECTED(Table[Brand])),
SUM(Table[Revenue])
)

View solution in original post

7 REPLIES 7
tolsen37
Regular Visitor

I was able to solve the issue by using the below DAX:

 

Calculation = IF (
CALCULATE(DISTINCTCOUNT(Table[Brand]), ALLSELECTED(Table[Brand]))
= COUNTROWS(ALLSELECTED(Table[Brand])),
SUM(Table[Revenue])
)

Anand24
Super User
Super User

Hi @tolsen37 ,
Create a calculated measure with below DAX and pull it into Values of matrix viz:

Revenue_Measure = 
IF(
    CALCULATE(
        SUM('Table'[Revenue]),
        'Table'[Brand Name] = "Brand 1"
    ) <> BLANK()
    &&
    CALCULATE(
        SUM('Table'[Revenue]),
        'Table'[Brand Name] = "Brand 2"
    ) <> BLANK(),
    SUM('Table'[Revenue]),
    BLANK()
)

Please make sure "Show items with no data" is unticked when you click on dropdown for Customer Name field in Rows.
Here's the result:

Anand24_0-1698865238752.png

 

PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

Hi @Anand24 

 

This works perfectly for the 2 brands.  However, is there a way to expand this calculation for an entire brand list?  We have a list of ~50 brands and the user would select whatever brands from a filter that they want to analyze.  

 

I could in theory make the if statement nested for each of the ~50 brands, but wanted to see if there was a more optimized method.  Thank you!

Daniel_PowerBI
Advocate I
Advocate I

Hi @tolsen37,

 

I did it in adventureWorks DB so you will need to adapt measure but here it is:

 
Value where both columns have value =
IF(
       CALCULATE( SUM(FactInternetSales[TotalProductCost]),DimGeography[CountryRegionCode] = "Blank 1") <> BLANK()
    && CALCULATE( SUM(FactInternetSales[TotalProductCost]),DimGeography[CountryRegionCode] = "Blank 2") <> BLANK()
    ,SUM(FactInternetSales[TotalProductCost])
    ,BLANK()
 )
 
Here is the ouput before and after:
Daniel_PowerBI_0-1698861448558.png

 

Regards,

If it works please mark as Solution

Hi @Daniel_PowerBI 

 

This works perfectly for the 2 brands.  However, is there a way to expand this calculation for an entire brand list?  We have a list of ~50 brands and the user would select whatever brands from a filter that they want to analyze.  

 

I could in theory make the if statement nested for each of the ~50 brands, but wanted to see if there was a more optimized method.  Thank you!

VijayP
Super User
Super User

@tolsen37  Is that you want to show only customer where revenue is there for both brands and others you want to hide? 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Hi @VijayP ,

 

Yes, that is correct.  Only show customers which have revenue for both brands and hide the other customers.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.