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() )
)
)
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
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
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
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 offeringVAR _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 ClientVAR _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 offeringRETURNIF (ISBLANK ( _selectedClient ) || _selectedHasOffer = 0,BLANK (),IF ( ISBLANK ( _peerSales ), _potentialValue, _peerSales ))