Forum Discussion
DAX Help
- 7 months ago
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() )
)
)
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.