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

            )

    )

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