Selected Value with lookups HELP

[gsed99]

Hello,

I am trying to create a report where a user can select a single Account ID and it will match that account to others based on the associated data of the selected account.

For example, I have 5 tiers for matching:

1 - Same Account ID

2- Same Parent Account + Country

3- Same Parent Account

4- Same Region + Industry

5- Same Industry

I created a calcuated measure for the selectedvalue which I put in a slicer and 5 separate measures that return each value 1-5 of the selectedvalue. I then want to match the returned values 1-5 against each row in a table to determine which level they match on, meaning if the row has the same region and industry but nothing above it, the value would be 4, and if only the industry matches the result in that row will be 5.

I am having an issue since the selectedvalue does not exist at the row level and I am trying to integrate with a live table.

Any help would be greatly appreciated!

Example: 
I have Account A12345 selected in the slicer, but my Level 1 Value formula aiming to return that value is not working as it shows the current rows Account ID when it should show A12345 all the way down. 

Level 1 Value =

CALCULATE (

    MAX ( 'Account Details'[Account Number] ),

    FILTER ( 'Account Details', 'Account Details'[Account Number] = [Selected ID] )

)

Selected ID = SELECTEDVALUE('Account Details'[Account Number],0)

[v-tangjie-msft]

Hi @gsed99 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a measure. 

Level 1 Value C = 
VAR _Selected_ID = SELECTEDVALUE('Account Details'[Account Number])
RETURN
    CALCULATE (
        MAX ( 'Account Details'[Shipping Country] ),
        FILTER ( ALLSELECTED('Account Details'), 'Account Details'[Account Number] = _Selected_ID )
    )

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[gsed99]

I tried implementing your formulas and they seem to get closer, but the main issue I am having is when I filter on the Account Number, it filters the table for just that ID, when I want it to inform the formulas but not literally filter the table for ID that is being compared against

[v-tangjie-msft · Accepted solution]

Hi @gsed99 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a slicer table and a measure. 

Slicer = VALUES('Table'[Account ID])

Cohort = 
var _industry=CALCULATE(MAX('Table'[Industry]),FILTER(ALLSELECTED('Table'),[Account ID]=SELECTEDVALUE('Slicer'[Account ID])))
var _country=CALCULATE(MAX('Table'[Country]),FILTER(ALLSELECTED('Table'),[Account ID]=SELECTEDVALUE('Slicer'[Account ID])))
RETURN IF(MAX('Table'[Account ID])=SELECTEDVALUE('Slicer'[Account ID]),"1",IF(MAX('Table'[Industry])=_country,"2",IF(MAX('Table'[Country])=_country,"3","Other")))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[gsed99]

Thank you this worked!

[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

[gsed99]

To simplify, how can I get a recurring row-level return from the SELECTEDVALUE measure?

The query below is giving me the max country of the whole table, I only want it filtered on the SELECTEDVALUE

Level 1 Value C =

VAR AccountIDValue = SELECTEDVALUE('Account Details'[Account Number])

RETURN

    CALCULATE (

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

        FILTER ( 'Account Details', 'Account Details'[Account Number] = [Selected ID] )

    )