Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
8 months ago
Solved

DAX Help

Dear Experts,   I'm building a Power BI matrix that compares peer clients against a selected client's service offerings. The goal is to identify which peers have or don’t have the same offerings as...
Need Help
Show and Tell
  • vjnvinod's avatar
    vjnvinod
    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() )

            )

    )

HarishKM's avatar
HarishKM
Super User
8 months ago

vjnvinod Hey,
I am taking some assumption for your requirement and creating dax based on that.

 

Assumptions

  • [Sales] = SUM of Pipeline[Total Value Of Potential Sale].
  • Matrix: Rows = Pipeline[Prospect Client Name], Columns = Pipeline[Service Offering].
  • Visual-level filter: PeerFilter = 1 (your existing measure).

Key measure (show selected client’s value when peer lacks the offering)
CrossSellPotential =
VAR SelectedClient = SELECTEDVALUE(ClientSlicerTable[Prospect Client Name])
VAR RowClient = MAX(Pipeline[Prospect Client Name])
VAR CurrOffering = SELECTEDVALUE(Pipeline[Service Offering])
VAR SelValue =
CALCULATE(
[Sales],
REMOVEFILTERS(Pipeline[Prospect Client Name]),
Pipeline[Prospect Client Name] = SelectedClient,
Pipeline[Service Offering] = CurrOffering
)
VAR PeerValue = [Sales]
RETURN
IF(
ISBLANK(SelectedClient) || RowClient = SelectedClient,
BLANK(),
IF( SelValue > 0 && (ISBLANK(PeerValue) || PeerValue = 0),
SelValue, -- potential = selected client’s value
BLANK() -- hide if peer already has the offering or selected lacks it
)
)

Optional flag (to filter/highlight only gaps)
CrossSellFlag =
VAR SelectedClient = SELECTEDVALUE(ClientSlicerTable[Prospect Client Name])
VAR CurrOffering = SELECTEDVALUE(Pipeline[Service Offering])
VAR SelHas =
CALCULATE(
[Sales] <> 0,
REMOVEFILTERS(Pipeline[Prospect Client Name]),
Pipeline[Prospect Client Name] = SelectedClient,
Pipeline[Service Offering] = CurrOffering
)
VAR PeerHas = NOT ISBLANK([Sales]) && [Sales] <> 0
RETURN IF(SelHas && NOT PeerHas, 1, 0)

How to use

  • Put CrossSellPotential in Values.
  • Keep PeerFilter = 1 on visual to show only peers.
  • Optionally add a visual-level filter CrossSellFlag = 1 to show only gap cells.

Few more suggestion- 

  • If your [Sales] can have duplicates per offering, SelValue sums them; switch to MAX/AVERAGEX if needed.
  • Ensure the client slicer is single-select; otherwise wrap logic with HASONEVALUE.

 


Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.