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

            )

    )

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