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() )
)
)
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() )
)
)
If this solves your issue, please mark this as an "Accepted Solution" to help others in the community
Best regards,
Vishwanath