Help with lookup from SELECTEDVALUE

[gsed99]

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 that account at the row level to compare with other accounts.

I am having a problem getting the calculated column to filter and it returns the MAX country of the whole table.

I am having problems using the slicer and not having it filter the whole table for just the one chosen Account ID, I just want it to update the value so I can use it for the lookup. 

Please help! Thanks!

I am trying the below but not getting it to work

Level 1 Value C =

    CALCULATE (

        MAX ( 'Account Details'[Shipping Country] ),

        FILTER ( 'Account Details', 'Account Details'[Account Number] = SELECTEDVALUE('Account Details'[Account Number] )

    ))

I am also trying

TEST = LOOKUPVALUE('Account Details'[Account Name],'Account Details'[Account Number],SELECTEDVALUE('Account Details'[Account Number]))

[lbendlin]

Calculated tables and columns are immutable and cannot be impacted by filters.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[gsed99]

Sorry updated link to shared access

[lbendlin]

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]

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

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[gsed99]

Thanks, please see link below with example file and dummy data

https://docs.google.com/spreadsheets/d/1B4k8l4CqzTWZKTpy4ImLyyQYiay1La_E/edit?usp=drive_link&ouid=10...

[gsed99]

Thank you, here is an example excel file with dummy data to illustrate the contect, change the slicer up top to see the changes below and the underlying formulas

I am wanting the user to select an ID by which row level logic can be evaluated against to show the relationship between the ID chosen and the ID in each row.

https://docs.google.com/spreadsheets/d/1W6MoKR5h7uSwCFqFq7axznNbl8sI2K-S/edit?pli=1#gid=1257160936