DAX Help

[vjnvinod]

Dear Experts,

I'm building a Power BI matrix that compares peer clients against a selected client's service offerings. The goal is to identify which peers have or don’t have the same offerings as the selected client, and flag those gaps as potential cross-sell opportunities

Data Model:

• Pipeline table with columns: Prospect Client Name, Service Offering, Total Value Of Potential Sale.
• ClientSlicerTable (disconnected from the Pipeline table) used to select a single client.)
• Peer clients are filtered using a PeerFilter measure (same sector, excluding selected client) which is set to 1 based on the below measure
  PeerFilter =

  VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )

  VAR _selectedSector = CALCULATE(

      MAX( Pipeline[Client Sector] ),

      ALL( Pipeline ),

      Pipeline[Prospect Client Name] = _selectedClient

  )

  VAR _rowSector = MAX( Pipeline[Client Sector] )

  VAR _rowClient = MAX( Pipeline[Prospect Client Name] )

  RETURN

      IF( NOT ISBLANK(_selectedClient) &&

          _rowSector = _selectedSector &&

          _rowClient <> _selectedClient,

          1,

          0

      )
  I need to display the potential cross-selling value for peer clients who currently have no sales (blank or 0) for service offerings that the selected client already has. The goal is to identify where peers are missing offerings that the selected client provides, and estimate the potential value they could generate if they adopted those offerings
• to do that i have used the below measure 
  CrossSellSales_Final =

  VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )

  VAR _currOffering   = SELECTEDVALUE( Pipeline[Service Offering] )

  VAR _selectedHasOffer =

      CALCULATE(

          COUNTROWS( Pipeline ),

          REMOVEFILTERS( Pipeline[Prospect Client Name] ),       -- ignore current peer filter

          Pipeline[Prospect Client Name] = _selectedClient,

          Pipeline[Service Offering]    = _currOffering

      )

  VAR _peerSales = [sales]

  VAR _baseResult =

      IF( ISBLANK( _selectedClient ), BLANK(),

          IF( _selectedHasOffer = 0, BLANK(),

              IF( NOT ISBLANK( _peerSales ), _peerSales, 0 )

          )

      )

  RETURN

      _baseResult,  this measure doesn't give me what i am looking for

  

  in the above example Financial statemnt audit and Financial Statement review are the sales offerings for ANB capital but in peer Client table, i only want to see, if peer client doesn't have both or one of the offerings, their potential sales value should be picked from the first table of Selected clients.

  I hope i was able to explain the requirement, might be a small dax fix in my current dax measure, would be great to get some support

[AshokKunwar]

Re: Identifying Cross-Sell Opportunities in Matrix with Disconnected Table

​Body:

Hello! This is a classic "Gap Analysis" requirement. The issue in your current measure is that _peerSales is returning BLANK for the gaps, and the logic isn't explicitly fetching the "Potential Value" from the Selected Client to fill that gap.

​To achieve this, you need to calculate the value the Selected Client has for that specific offering and use that as the "Potential" value when the peer has no sales.

​Try this updated measure:

CrossSellSales_Final = 

VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )

VAR _currOffering = SELECTEDVALUE( Pipeline[Service Offering] )

-- 1. Get the value the Selected Client has for this offering

VAR _potentialValue = 

    CALCULATE(

        SUM( Pipeline[Total Value Of Potential Sale] ),

        REMOVEFILTERS( Pipeline[Prospect Client Name] ),

        Pipeline[Prospect Client Name] = _selectedClient,

        Pipeline[Service Offering] = _currOffering

    )

-- 2. Check if the Peer Client in the current row has sales

VAR _peerSales = [sales]

-- 3. Logic: If Selected Client HAS it AND Peer DOES NOT have it, show Potential Value

RETURN

    IF( ISBLANK(_selectedClient), BLANK(),

        IF( _potentialValue > 0 && ISBLANK(_peerSales), 

            _potentialValue, 

            _peerSales 

        )

    )

Why this works:

_potentialValue: Instead of just counting rows, we use SUM to get the actual dollar value from the Selected Client's record for that offering.

The IF Condition: It checks if _potentialValue exists (meaning the Selected Client uses this service) and if _peerSales is BLANK. If both are true, it "plugs the gap" with the potential value.

Note: Ensure your Matrix rows are Prospect Client Name (from the Pipeline table) and columns are Service Offering.

I hope this helps you identify those cross-sell opportunities! If this resolves your issue, please mark this post as an "Accepted Solution" to help others in the community. Happy New Year!

Best regards,

Vishwanath

[vjnvinod]

that doesn't work either, it expands all my offerings on the 2nd table while i want to restrict the offerings of what being selected

[AshokKunwar]

Thank you for the feedback! I see what happened—the previous measure was returning a value for every offering in your database. To restrict the rows so you only see the offerings the Selected Client has, we need to add a filter check at the start of the measure.

​Try this updated version:

CrossSellSales_Final = 

VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )

VAR _currOffering = SELECTEDVALUE( Pipeline[Service Offering] )

-- 1. Check if the Selected Client actually has this offering

VAR _selectedHasOffer = 

    CALCULATE(

        COUNTROWS( Pipeline ),

        REMOVEFILTERS( Pipeline[Prospect Client Name] ),

        Pipeline[Prospect Client Name] = _selectedClient,

        Pipeline[Service Offering] = _currOffering

    )

-- 2. Get the potential value from the Selected Client

VAR _potentialValue = 

    CALCULATE(

        SUM( Pipeline[Total Value Of Potential Sale] ),

        REMOVEFILTERS( Pipeline[Prospect Client Name] ),

        Pipeline[Prospect Client Name] = _selectedClient,

        Pipeline[Service Offering] = _currOffering

    )

VAR _peerSales = [sales]

-- 3. Logic: Only show data IF the selected client has the offering

RETURN

IF( ISBLANK(_selectedClient) || _selectedHasOffer = 0, 

    BLANK(), 

    IF( ISBLANK(_peerSales), _po

tentialValue, _peerSales )

)

Why this fixes the "Expanding" issue:

The IF(_selectedHasOffer = 0, BLANK(), ...) part is the key. In Power BI, if a measure returns BLANK(), the Matrix automatically hides that row or column. By forcing a BLANK whenever the Selected Client doesn't own the offering, the 2nd table will shrink to match only the Selected Client's portfolio.

I hope this restricted view is exactly what you are looking for! If this resolves the expansion issue, please mark this as an "Accepted Solution."

Best regards,

Vishwanath

[vjnvinod]

@AshokKunwar 

here is the output, the problem remains the same, its not filling the gap

CrossSellSales_Final =

VAR _selectedClient =

    SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )

VAR _currOffering =

    SELECTEDVALUE ( Pipeline[Service Offering] ) -- 1. Check if the Selected Client actually has this offering

VAR _selectedHasOffer =

    CALCULATE (

        COUNTROWS ( Pipeline ),

        REMOVEFILTERS ( Pipeline[Prospect Client Name] ),

        Pipeline[Prospect Client Name] = _selectedClient,

        Pipeline[Service Offering] = _currOffering

    ) -- 2. Get the potential value from the Selected Client

VAR _potentialValue =

    CALCULATE (

        SUM ( Pipeline[ Total Value Of Potential Sale ] ),

        REMOVEFILTERS ( Pipeline[Prospect Client Name] ),

        Pipeline[Prospect Client Name] = _selectedClient,

        Pipeline[Service Offering] = _currOffering

    )

VAR _peerSales = [sales] -- 3. Logic: Only show data IF the selected client has the offering

RETURN

    IF (

        ISBLANK ( _selectedClient ) || _selectedHasOffer = 0,

        BLANK (),

        IF ( ISBLANK ( _peerSales ), _potentialValue, _peerSales )

    )

[AshokKunwar]

The reason the gap isn't filling is likely a Visual Context issue. In a Matrix, if a Peer hasn't bought a service, that "Cell" doesn't technically exist for the measure to run in. We need to ensure _currOffering captures the context from the Matrix Columns rather than the Peer's data.

​Try this specific adjustment to your variables

CrossSellSales_Final =
VAR _selectedClient = SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )

-- FIX: Use ALLSELECTED to ensure we grab the offering from the Matrix Column header,
-- even if the Peer has no record for it.
VAR _currOffering = SELECTEDVALUE ( 'Pipeline'[Service Offering] )

-- 1. Check if the Selected Client has this offering
VAR _selectedHasOffer =
CALCULATE (
COUNTROWS ( Pipeline ),
ALL( Pipeline ), -- Complete clear to find the Selected Client's portfolio
Pipeline[Prospect Client Name] = _selectedClient,
Pipeline[Service Offering] = _currOffering
)

-- 2. Get the potential value from the Selected Client
VAR _potentialValue =
CALCULATE (
SUM ( Pipeline[Total Value Of Potential Sale] ),
ALL( Pipeline ),
Pipeline[Prospect Client Name] = _selectedClient,
Pipeline[Service Offering] = _currOffering
)

VAR _peerSales = [sales]

-- 3. The Logic
RETURN
IF ( ISBLANK ( _selectedClient ), BLANK(),
IF ( _selectedHasOffer > 0,
IF ( ISBLANK ( _peerSales ) || _peerSales = 0, _potentialValue, _peerSales ),
BLANK()
)
)

One Final Visual Step:

If the gaps still don't show, click on the Service Offering field in the "Columns" bucket of your Matrix visual, click the down arrow, and select "Show items with no data." This forces Power BI to create the "empty buckets" so our DAX can fill them with your _potentialValue.

​This should definitely bridge the gap! Please let me know if this works—I am committed to getting this right for you.

​Best regards,

Vishwanath

[vjnvinod]

@AshokKunwar 

thanks Ashok, appreciate your time and sincere effort, unfortunately it still doesn't work

i think i missed one context, not sure if it has to do something with not filling the gaps with potential value

 and the measure used is

PeerFilter =

VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )

VAR _selectedSector = CALCULATE(

    MAX( Pipeline[Client Sector] ),

    ALL( Pipeline ),

    Pipeline[Prospect Client Name] = _selectedClient

)

VAR _rowSector = MAX( Pipeline[Client Sector] )

VAR _rowClient = MAX( Pipeline[Prospect Client Name] )

RETURN

    IF( NOT ISBLANK(_selectedClient) &&

        _rowSector = _selectedSector &&

        _rowClient <> _selectedClient,

        1,

        0

    )

[AshokKunwar]

Try this 👇

PeerFilter_Updated =
VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )

-- Get the sector of the client selected in the slicer
VAR _selectedSector =
CALCULATE(
MAX( Pipeline[Client Sector] ),
ALL( Pipeline ),
Pipeline[Prospect Client Name] = _selectedClient
)

-- Get the sector of the Peer currently on the Matrix Row
VAR _rowClient = SELECTEDVALUE( Peers[Prospect Client Name] )
VAR _rowSector =
CALCULATE(
MAX( Pipeline[Client Sector] ),
ALL( Pipeline ),
Pipeline[Prospect Client Name] = _rowClient
)

RETURN
IF(
NOT ISBLANK(_selectedClient) &&
_rowSector = _selectedSector &&
_rowClient <> _selectedClient,
1,
0
)

I hope this helps you stabilize your automation! If this architectural workaround resolves your issue, please mark this as an "Accepted Solution" to help others in the community.

Best regards,

Vishwanath

[vjnvinod]

in your code i see you have 

VAR _rowClient = SELECTEDVALUE( Peers[Prospect Client Name] )

i don't have a table called Peers, Only 2 tables, one is Pipeline and other one is ClientSlicerTable (which is basically derived from Pipeline table)

with a formula like this

ClientSlicerTable = VALUES( Pipeline[Prospect Client Name] )

[AshokKunwar]

Last option:

​Step 1: Create the Peer Table

​You need a table that isn't filtered by your slicer. Since you already have the logic for ClientSlicerTable, just create one more identical table for your Matrix rows:

MatrixPeers = VALUES( Pipeline[Prospect Client Name] )

[Note: Do not create a relationship between MatrixPeers and any other table in the Model view.]

Step 2: Use the Disconnected Pattern

By using MatrixPeers on the Rows of your Matrix and Pipeline[Service Offering] on the Columns, you force Power BI to create a grid of every client vs. every service.

Now, use this measure to fill that grid:

CrossSell_Final = 

VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )

VAR _currOffering = SELECTEDVALUE( Pipeline[Service Offering] )

VAR _rowClient = SELECTEDVALUE( MatrixPeers[Prospect Client Name] )

-- 1. Identify the Sector of the client selected in the Slicer

VAR _selectedSector = 

    CALCULATE(

        MAX( Pipeline[Client Sector] ),

        REMOVEFILTERS( Pipeline ),

        Pipeline[Prospect Client Name] = _selectedClient

    )

-- 2. Identify the Sector of the Peer on the current Matrix Row

VAR _rowSector = 

    CALCULATE(

        MAX( Pipeline[Client Sector] ),

        REMOVEFILTERS( Pipeline ),

        Pipeline[Prospect Client Name] = _rowClient

    )

-- 3. Check if the Sliced Client actually has this service

VAR _selectedHasOffer = 

    CALCULATE(

        COUNTROWS( Pipeline ),

        REMOVEFILTERS( Pipeline ),

        Pipeline[Prospect Client Name] = _selectedClient,

        Pipeline[Service Offering] = _currOffering

    )

-- 4. Get Actual Sales for the Peer (using TREATAS to bridge the disconnected row)

VAR _peerSales = 

    CALCULATE(

        SUM( Pipeline[Sales] ),

        TREATAS( { _rowClient }, Pipeline[Prospect Client Name] )

    )

-- 5. The Final Logic

RETURN

IF( _rowClient = _selectedClient || _rowSector <> _selectedSector, 

    BLANK(), -- Don't show the selected client or clients in other sectors

    IF( _selectedHasOffer > 0,

        IF( ISBLANK(_peerSales) || _peerSales = 0, 

            "Potential", -- This fills the gap!

            FORMAT(_peerSales, "Cu

rrency") 

        ),

        BLANK()

    )

)

Why this solves it:

Context: Since MatrixPeers is disconnected, it doesn't care if a peer has sales for "Cloud Services" or not. It will stay on the Matrix row regardless.

The Bridge: TREATAS manually tells the measure: "Take this name from the disconnected row and pretend it's filtering the main Pipeline table just for this calculation."

The Gap Filler: Because the row doesn't disappear, the IF(ISBLANK(_peerSales)...) logic finally has a "place" to display the word "Potential."

I hope this restricted view is exactly what you are looking for! If this resolves the expansion issue, please mark this as an "Accepted Solution."

Best regards,

Vishwanath

[vjnvinod]

@AshokKunwar   some error

[AshokKunwar]

 for this error 

CrossSell_Final_Fixed = 

VAR _selectedClient = SELECTEDVALUE( 'ClientSlicerTable'[Prospect Client Name] )

VAR _currOffering = SELECTEDVALUE( 'Pipeline'[Service Offering] )

VAR _rowClient = SELECTEDVALUE( 'Pipeline'[Prospect Client Name] )

-- 1. Get the Sector of the client selected in the slicer

VAR _selectedSector = 

    CALCULATE(

        MAX( 'Pipeline'[Client Sector] ),

        REMOVEFILTERS( 'Pipeline' ),

        'Pipeline'[Prospect Client Name] = _selectedClient

    )

-- 2. Get the Sector of the Peer on the current row

VAR _rowSector = MAX( 'Pipeline'[Client Sector] )

-- 3. Check if the Sliced Client has this offering

VAR _selectedHasOffer = 

    CALCULATE(

        COUNTROWS( 'Pipeline' ),

        REMOVEFILTERS( 'Pipeline' ),

        'Pipeline'[Prospect Client Name] = _selectedClient,

        'Pipeline'[Service Offering] = _currOffering

    )

-- 4. Get Actual Sales for this peer

VAR _actualSales = [sales]

-- 5. Final Logic

RETURN

IF( _rowClient = _selectedClient || _rowSector <> _selectedSector, 

    BLANK(),

    IF( _selectedHasOffer > 0,

        COALESCE( _actualSales, 0 ), -- COALESCE fills the g

ap with 0

        BLANK()

    )

)

Why this fixes the error:

Removed FORMAT(): By removing the string conversion, we eliminate the "out of range for format string" error.

COALESCE(..., 0): This is the key to "filling the gap." It ensures that even if a peer has no sales, the measure returns a 0, forcing Power BI to display the row and column context.

Final Configuration Steps

To make the 0s look like currency and ensure the gaps stay visible:

Format as Currency: Select your new measure in the Data pane. Go to Measure tools at the top and set the Format to Currency. This way, it stays a number but looks like money.

Force the Grid: * In your Matrix visual, click the down-arrow on Prospect Client Name (in Rows) and select "Show items with no data."

Do the same for Service Offering (in Columns).

Conditional Formatting (Optional): To make the "Gaps" (the 0s) stand out, go to Format visual > Cell elements, turn on Background color for this measure, and set a rule: If value is 0, then color is Light Orange.

[vjnvinod]

@AshokKunwar 

doesn't work either, see the output below

[vjnvinod · Accepted solution]

Finally figured it out 

CrossSell (Only fill from selected when peer blank) =

VAR SelectedClient = SELECTEDVALUE ( 'ClientSlicerTable'[Prospect Client Name] )

VAR RowClient      = SELECTEDVALUE ( 'MatrixPeers'[Prospect Client Name] )

VAR CurrOffering   = SELECTEDVALUE ( 'Pipeline'[Service Offering] )

RETURN

IF (

    NOT ISINSCOPE('MatrixPeers'[Prospect Client Name]) || NOT ISINSCOPE('Pipeline'[Service Offering]),

    BLANK(),

    VAR SelectedSector =

        CALCULATE ( MAX('Pipeline'[Client Sector]), REMOVEFILTERS('Pipeline'),

            TREATAS({SelectedClient}, 'Pipeline'[Prospect Client Name]) )

    VAR RowSector =

        CALCULATE ( MAX('Pipeline'[Client Sector]), REMOVEFILTERS('Pipeline'),

            TREATAS({RowClient}, 'Pipeline'[Prospect Client Name]) )

    VAR PeerSales =

        CALCULATE ( [sales], REMOVEFILTERS('Pipeline'),

            TREATAS({RowClient}, 'Pipeline'[Prospect Client Name]),

            TREATAS({CurrOffering}, 'Pipeline'[Service Offering]) )

    VAR SelectedClientSalesForThisOffering =

        CALCULATE ( [sales], REMOVEFILTERS('Pipeline'),

            TREATAS({SelectedClient}, 'Pipeline'[Prospect Client Name]),

            TREATAS({CurrOffering}, 'Pipeline'[Service Offering]) )

    RETURN

        IF (

            RowClient = SelectedClient || RowSector <> SelectedSector || ISBLANK(SelectedSector),

            BLANK(),

            IF ( ISBLANK(PeerSales), SelectedClientSalesForThisOffering, BLANK() )

        )

)

[AshokKunwar]

If this solves your issue, please mark this as an "Accepted Solution" to help others in the community.

​Best regards,

Vishwanath

[v-echaithra]

HI @vjnvinod ,

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know and provide the sample data as mentioned in previous reply, we’re happy to help!

Thank you 
Chaithra E.

[v-echaithra]

Hi @vjnvinod ,

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance, please provide the sample Pbix file. How to provide sample data in the Power BI Forum

You can refer the following link to upload the file to the community.
How to upload PBI in Community

We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[vjnvinod]

@v-echaithra 

No this issue is not resolved yet, i will be posting a sample pbix shortly

[Ashish_Mathur]

Hi,

Could you share some dummy data set to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[HarishKM]

@vjnvinod Hey,
I am taking some assumption for your requirement and creating dax based on that.

Assumptions

• [Sales] = SUM of Pipeline[Total Value Of Potential Sale].
• Matrix: Rows = Pipeline[Prospect Client Name], Columns = Pipeline[Service Offering].
• Visual-level filter: PeerFilter = 1 (your existing measure).

Key measure (show selected client’s value when peer lacks the offering)
CrossSellPotential =
VAR SelectedClient = SELECTEDVALUE(ClientSlicerTable[Prospect Client Name])
VAR RowClient = MAX(Pipeline[Prospect Client Name])
VAR CurrOffering = SELECTEDVALUE(Pipeline[Service Offering])
VAR SelValue =
CALCULATE(
[Sales],
REMOVEFILTERS(Pipeline[Prospect Client Name]),
Pipeline[Prospect Client Name] = SelectedClient,
Pipeline[Service Offering] = CurrOffering
)
VAR PeerValue = [Sales]
RETURN
IF(
ISBLANK(SelectedClient) || RowClient = SelectedClient,
BLANK(),
IF( SelValue > 0 && (ISBLANK(PeerValue) || PeerValue = 0),
SelValue, -- potential = selected client’s value
BLANK() -- hide if peer already has the offering or selected lacks it
)
)

Optional flag (to filter/highlight only gaps)
CrossSellFlag =
VAR SelectedClient = SELECTEDVALUE(ClientSlicerTable[Prospect Client Name])
VAR CurrOffering = SELECTEDVALUE(Pipeline[Service Offering])
VAR SelHas =
CALCULATE(
[Sales] <> 0,
REMOVEFILTERS(Pipeline[Prospect Client Name]),
Pipeline[Prospect Client Name] = SelectedClient,
Pipeline[Service Offering] = CurrOffering
)
VAR PeerHas = NOT ISBLANK([Sales]) && [Sales] <> 0
RETURN IF(SelHas && NOT PeerHas, 1, 0)

How to use

• Put CrossSellPotential in Values.
• Keep PeerFilter = 1 on visual to show only peers.
• Optionally add a visual-level filter CrossSellFlag = 1 to show only gap cells.

Few more suggestion- 

• If your [Sales] can have duplicates per offering, SelValue sums them; switch to MAX/AVERAGEX if needed.
• Ensure the client slicer is single-select; otherwise wrap logic with HASONEVALUE.

Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.

[grazitti_sapna]

Hi @vjnvinod,

It seems the issue is with the logic you are implementing,

Try below DAXs to fix this issue

Sales :=
SUM ( Pipeline[Total Value Of Potential Sale] )

Selecting Client’s Sales for Current Offering

SelectedClient_Offering_Sales :=
VAR _selectedClient =
SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )
VAR _currOffering =
SELECTEDVALUE ( Pipeline[Service Offering] )
RETURN
CALCULATE (
[Sales],
REMOVEFILTERS ( Pipeline[Prospect Client Name] ),
Pipeline[Prospect Client Name] = _selectedClient,
Pipeline[Service Offering] = _currOffering
)

Finally calculate cross sell 

CrossSellSales_Final :=
VAR _selectedClient =
SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )

VAR _peerSales =
[Sales]

VAR _selectedClientSales =
[SelectedClient_Offering_Sales]

RETURN
IF (
ISBLANK ( _selectedClient ),
BLANK(),

-- Selected client does NOT have this offering → hide row
IF (
ISBLANK ( _selectedClientSales ) || _selectedClientSales = 0,
BLANK(),

-- Peer HAS the offering → show actual peer sales
IF (
NOT ISBLANK ( _peerSales ) && _peerSales > 0,
_peerSales,

-- Peer does NOT have it → show potential from selected client
_selectedClientSales
)
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!