Forum Discussion

Icon for Resolver II rank

Resolver II

9 years ago

Solved

Dax to lookup from multiple values

I have a DAX filter to see if the user is merchant but want to extend to see if they belong to Manger or Director. Any way to extend this filter?

='Ownership'[Merchant]=LOOKUPVALUE('Ownership'[Merchant], 'Ownership'[UPN], USERNAME())

Anonymous
9 years ago
Gotcha.

In that case you will have to get more fancy :)

The equivalent of this:
'Ownership'[Merchant]=LOOKUPVALUE('Ownership'[Merchant], 'Ownership'[UPN], USERNAME())

is roughly:
'Ownership'[Merchant]=CALCULATE(VALUES('Ownership'[Merchant]), FILTER(Ownership, Ownership[UPN] = USERNAME()))

so to add more conditions:
'Ownership'[Merchant]=CALCULATE(VALUES('Ownership'[Merchant]), FILTER(Ownership, Ownership[UPN] = USERNAME() || Ownership[FirstName] = "Bob"))

4 Replies

Anonymous
Not applicable
9 years ago
https://msdn.microsoft.com/en-us/library/gg492170.aspx

The LOOKUPVALUE function can look at as many columns as you want...
- sgsukumaran
  Resolver II
  9 years ago
  Anonymous
  
  Thats correct but I want the "OR" condition to be applied. The User can be a mechant or manager or Director
  - Anonymous
    Not applicable
    9 years ago
    Gotcha.
    
    In that case you will have to get more fancy :)
    
    The equivalent of this:
    'Ownership'[Merchant]=LOOKUPVALUE('Ownership'[Merchant], 'Ownership'[UPN], USERNAME())
    
    is roughly:
    'Ownership'[Merchant]=CALCULATE(VALUES('Ownership'[Merchant]), FILTER(Ownership, Ownership[UPN] = USERNAME()))
    
    so to add more conditions:
    'Ownership'[Merchant]=CALCULATE(VALUES('Ownership'[Merchant]), FILTER(Ownership, Ownership[UPN] = USERNAME() || Ownership[FirstName] = "Bob"))
v-huizhn-msft
Microsoft Employee
9 years ago
Hi sgsukumaran,

If you still have not resolved your issue based on the solution above. Please post some samle data for further analysis. So that we can post DAX which is close to your requirement.

Thanks,
Angelia

Related content