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

[tolsen37]

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?

[tolsen37 · Accepted 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])
)

[Anand24]

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 !!! LinkedIn

[tolsen37]

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]

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:

Regards,

If it works please mark as Solution

[tolsen37]

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]

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

[tolsen37]

Hi @VijayP ,

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