Forum Discussion

gsed99's avatar
gsed99
Icon for Helper III rankHelper III
2 years ago

Help with lookup from SELECTEDVALUE

Hello, In a calculated column, I want to return a single value from the same table based on the SELECTEDVALUE. Meaning I have a slicer and choose Account A12345 and I want to return the country for ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

I don't understand how you arrive at the 1/3/3 Cohort logic. Please elaborate.

 

And to be clear - this can only be done via a measure, it is not possible to create a calculated table from a slicer selection.

gsed99's avatar
gsed99
Icon for Helper III rankHelper III
2 years ago

I've got this working via a calculated table and measure, but now I want to join in opportunity data which is joined to my account table. The problem is when I add the measure in with opportunity data it fails. Would that be a join issue or do I need to modify my measure to incorporate values from the opportunity table?

This setup works fine but only when using the Account Details table.

Table:

Cohort =
FILTER (
    VALUES ( 'Account Details'[Account Number] ),
    NOT ISBLANK ( 'Account Details'[Account Number] )
)
 
Measure:
Cohort =
var _account=CALCULATE(MAX('Account Details'[Account Number]),FILTER(ALLSELECTED('Account Details'),[Account Number]=SELECTEDVALUE('Cohort'[Account Number])))
var _country=CALCULATE(MAX('Account Details'[Shipping Country]),FILTER(ALLSELECTED('Account Details'),[Account Number]=SELECTEDVALUE('Cohort'[Account Number])))
var _industry=CALCULATE(MAX('Account Details'[Industry]),FILTER(ALLSELECTED('Account Details'),[Account Number]=SELECTEDVALUE('Cohort'[Account Number])))
var _Ent_Country=CALCULATE(MAX('Account Details'[Ent Name + Country]),FILTER(ALLSELECTED('Account Details'),[Account Number]=SELECTEDVALUE('Cohort'[Account Number])))
var _Enterprise=CALCULATE(MAX('Account Details'[Enterprise Account Name]),FILTER(ALLSELECTED('Account Details'),[Account Number]=SELECTEDVALUE('Cohort'[Account Number])))
var _Reg_Industry=CALCULATE(MAX('Account Details'[Region + Industry]),FILTER(ALLSELECTED('Account Details'),[Account Number]=SELECTEDVALUE('Cohort'[Account Number])))
RETURN IF(MAX('Account Details'[Account Number])=_account,"1 - Account Match",IF(MAX('Account Details'[Ent Name + Country])=_Ent_Country,"2 - Ent + Country",IF(MAX('Account Details'[Enterprise Account Name])=_Enterprise,"3 - Ent Name",IF(MAX('Account Details'[Region + Industry])=_Reg_Industry,"4 - Region + Industry",IF(MAX('Account Details'[Industry])=_industry,"5 - Industry","Other")))))