Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Here are the current fields used to build the Matrix
Any ideas?
Solved! Go to Solution.
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])
)
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])
)
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:
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 !!! |
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!
Hi @tolsen37,
I did it in adventureWorks DB so you will need to adapt measure but here it is:
Regards,
If it works please mark as Solution
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!
@tolsen37 Is that you want to show only customer where revenue is there for both brands and others you want to hide?
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |