Forum Discussion
DAX Help
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() )
)
)
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
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
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
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] )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