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
gsed99's avatar
gsed99
Icon for Helper III rankHelper III
2 years ago

Sorry updated link to shared access

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