DAX filter variable x where 'agent' category z matches 'agent' category y.

[AlexD43]

Hi

'In period x Agent A can be the first 'leaduser'[fullname] of y leads, and the sold user of z contracts(partially, but not completely from y leads).

I have leads in by agent but I can't filter the sales by the same agents as leads and sales aren't connected in our system (data gathered separately).

I've figured I need to pull sales where 'solduser'[fullname] matches the 'leaduser[fullname] value identified in the table, but I can't seem to filter or search the result when 'solduser'[fullname] looks up from 'leaduser'[fullname].

leaduser'[fullname]	Total Leads		Additional Products Contracts - ignore lead date - ignore lead user
Agent A	6		CODE in Red below
Agent B	12		CODE in Red below
etc…	etc		CODE in Red below
…			CODE in Red below
Value of all contracts regardless of lead date	WORKS		Additional Products Contracts - ignore lead date = CALCULATE([Total Contracts - AdditionalProducts],all('Lead Date'[FullDate]))
Value of all contracts regardless of lead date and original lead user	DOESN’T WORK		Additional Products Contracts - ignore lead date - ignore lead user = CALCULATE([Total Contracts - AdditionalProducts],all('Lead Date'[FullDate]),lookupvalue(LeadUser[FullName],LeadUser[FullName],SoldUser[FullName]))

I'm a relative noob to DAX so be gentle, but I'm progressing quickly. 

Cheers and Thanks in advance

[MFelix]

Hi @AlexD43 ,

Be aware that measures in DAX are based on context so depending on the filters, visualization type, slicers, relationship between tables or other situations the result may vary.

To what I can understand from your description you need to get the full sum of your contracts for all dates and all users correct?

However I'm confused about the tables and relationships that you present and why you are making a lookupvalue.

Can you please share a sample of your data and relationship between the tables and expected result?

If you can share a mockup file would be great.

[AlexD43]

Hi Miguel

That was my attempt to look up the leaduser, return the solduser and return the results of that solduser.
Unfortunately I don't manage the raw data warehouse so a sample isn't achievable.

i need to know how many leads an agent took, regardless of whether they're sold or not, and how many contracts an agent sold, regardless of who the original lead was created by.

Thanks again.

[MFelix]

Sorry for the additional questions but your data preview is not maching the syntax of your measure so difficult to pinpoint the error.

• Are you create a measure or a calculated column?
• Is the sold user another column on your table?
• How are you setting up the visualization for this calculation based on the seller?
• How is this measure [Total Contracts - AdditionalProducts] calculated?

Are you abble to make a mockup of the data in your tables just a simple example with 4/5 lines and only with the information you need for this calculation in this case I'm seeing the following columns but believe there are more:

'Lead Date'[FullDate]

LeadUser[FullName]

SoldUser[FullName]

Totaleads

In theory and looking at your first table something similar to this should work:

Additional Products Contracts - ignore lead date - ignore lead user =
CALCULATE (
    [Total ContractsadditionalProducts],
    ALL ( 'Lead Date'[FullDate] ),
    FILTER (
        ALL ( SoldUser ),
        SoldUser[FullName] = SELECTEDVALUE ( LeadUser[FullName] )
    )
)

But again I don't have any relationship between the tables and need to understand the connection also between the SoldUser, LeadUser,LeadDate and Contracts table.

[AlexD43]

Looking at the result of that suggestion I think there is no leaduser key in the sales data, meaning there are no agents to select by. (Hoping this makes sense).

This is why I was kind of hoping there was a way to make a text match of 'Agent A' to match either the sales to the lead user name or vice versa.

[MFelix]

Hi @AlexD43 ,

Sorry once again but without knowing the model I cannot help you in more detail since as I refered you have several tables and column refered in your measure the way they relate is the basis of the calculations so whern you refer that there is no leaduse key in the sales data I have no idea what that means in your model.

You can create a match, using a lookup has you have or something similar but I'm only guessing without any details.

Can you share your file? If the information is sensitive you can share it by private message using a onedrive, google drive, we transfer or similar url.

Please remember to send out expected result also.