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() )

            )

    )

grazitti_sapna's avatar
grazitti_sapna
Super User
7 months ago

Hi vjnvinod,

 

It seems the issue is with the logic you are implementing,

 

Try below DAXs to fix this issue

 

Sales :=
SUM ( Pipeline[Total Value Of Potential Sale] )

 

Selecting Client’s Sales for Current Offering

SelectedClient_Offering_Sales :=
VAR _selectedClient =
SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )
VAR _currOffering =
SELECTEDVALUE ( Pipeline[Service Offering] )
RETURN
CALCULATE (
[Sales],
REMOVEFILTERS ( Pipeline[Prospect Client Name] ),
Pipeline[Prospect Client Name] = _selectedClient,
Pipeline[Service Offering] = _currOffering
)

 

Finally calculate cross sell 

 

CrossSellSales_Final :=
VAR _selectedClient =
SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )

VAR _peerSales =
[Sales]

VAR _selectedClientSales =
[SelectedClient_Offering_Sales]

RETURN
IF (
ISBLANK ( _selectedClient ),
BLANK(),

-- Selected client does NOT have this offering → hide row
IF (
ISBLANK ( _selectedClientSales ) || _selectedClientSales = 0,
BLANK(),

-- Peer HAS the offering → show actual peer sales
IF (
NOT ISBLANK ( _peerSales ) && _peerSales > 0,
_peerSales,

-- Peer does NOT have it → show potential from selected client
_selectedClientSales
)
)
)

 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

vjnvinod's avatar
vjnvinod
Impactful Individual
7 months ago

grazitti_sapna 

 

thank you, but unfortunately dax still doesn't work, for example in the below view for Peer client Abhudhabi Global market  doesn't have sales in RI-Financial Crimes, so it should pick up the value from selected clients sales value from RI-Financial crimes, in this case $45K, so this $45K becomes potential cross selling opportunity?

Not sure if i am able to explain this

 

 

 

  • amitchandak's avatar
    amitchandak
    Super User
    7 months ago

    vjnvinod , Have two measures like below, use the second measure M2 with ClientSlicerTable or pipeline Prospect Client Name

     

     

    M1 =
    VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )
    _tab = summarize(filter(Pipeline, Pipeline[Prospect Client Name] = _selectedClient ), Pipeline[Client Sector] )
    return
    Countrows(filter(Pipeline, Pipeline[Client Sector] in _tab))


    M2 = Countx(values(ClientSlicerTable[Prospect Client Name]) , If(ISBLANK([M1]),[Prospect Client Name], blank()))

     

    or

     

     

    M2 = Countx(values(Pipeline[Prospect Client Name] ) , If(ISBLANK([M1]),[Prospect Client Name], blank()))

     

    If both tables are joined first one should work. Else try second M1

    • vjnvinod's avatar
      vjnvinod
      Impactful Individual
      7 months ago

        

      Looks like there is some logical issue, unfortunately even co pilot is not putting the right solution

       

      M1 measure

      M1 =
      VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )
      _tab = summarize(filter(Pipeline, Pipeline[Prospect Client Name] = _selectedClient ), Pipeline[Client Sector] )
      return
      Countrows(filter(Pipeline, Pipeline[Client Sector] in _tab))

       

       

      The syntax for '_tab' is incorrect. (DAX(VAR _selectedClient = SELECTEDVALUE( ClientSlicerTable[Prospect Client Name] )_tab = summarize(filter(Pipeline, Pipeline[Prospect Client Name] = _selectedClient ), Pipeline[Client Sector] )returnCountrows(filter(Pipeline, Pipeline[Client Sector] in _tab)))).

       

      what copilot is giving is

       

      M1 =
      VAR _selectedClient =
          SELECTEDVALUE ( ClientSlicerTable[Prospect Client Name] )
      VAR _tab =
          CALCULATETABLE (
              VALUES ( Pipeline[Client Sector] ),
              FILTER ( Pipeline, Pipeline[Prospect Client Name] = _selectedClient )
          )
      RETURN
      COUNTROWS (
          FILTER ( Pipeline, Pipeline[Client Sector] IN _tab )
      )

       

      amitchandak