Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
7 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() )

            )

    )

vjnvinod's avatar
vjnvinod
Impactful Individual
7 months ago

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

 

 

AshokKunwar's avatar
AshokKunwar
Continued Contributor
7 months ago

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