DAX Help

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

AshokKunwar   some error

 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.

AshokKunwar 

doesn't work either, see the output below

vjnvinod 

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

​Best regards,

Vishwanath