- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
alternative to lookupvalue & related??
Hello,
I have 2 tables in which I have a many to 1 relationship between IDs. I need to look up the ID (text) from table 1 and return a measure value (number) in table 2. The measure is calculated from what if parameters. I understand that measures cannot be used in LOOKUPVALUE. RELATED does not also recognize measures.
Below is an example of my dataset.
Table 1 |
ID |
1 |
2 |
3 |
1 |
4 |
3 |
2 |
6 |
7 |
5 |
5 |
8 |
Table 2 | |
ID | Value (measure) |
1 | 15 |
2 | 23 |
3 | 78 |
4 | 54 |
5 | 2 |
6 | 36 |
7 | 14 |
8 | 5 |
My expected output is:
Table 1 | |
ID | Output |
1 | 15 |
2 | 23 |
3 | 78 |
1 | 15 |
4 | 54 |
3 | 78 |
2 | 23 |
6 | 36 |
7 | 14 |
5 | 2 |
5 | 2 |
8 | 5 |
Do I have any options, or do I need to rethink my approach? The what if parameters are an attempt to allow the end user to enter manual data to update the report.
(SWITCH?, maxx(filter(table1,table1(ID)=table2(ID)),table2(Value))???
Any help would be greatly appreciated!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @unknown917
Can you tell me if your problem is solved? If yes, please accept it as solution.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @unknown917
Thank you very much lbendlin for your prompt reply.
Here is a simple example of my design for your problem, try the following:
Here's some dummy data
Value (Measure) =
var _whatifparameter = SELECTEDVALUE('WhatIfParameter'[WhatIfParameter])
RETURN
IF(
ISFILTERED('WhatIfParameter'[WhatIfParameter]),
SELECTEDVALUE('Table 2'[Value]) * _whatifparameter,
SELECTEDVALUE('Table 2'[Value])
)
Create a measure.
Output =
MAXX(
FILTER(
'Table 2',
'Table 2'[ID] = SELECTEDVALUE('Table 1'[ID])
),
'Table 2'[Value (Measure)]
)
Here is the result.
If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you already have a relationship then let it do the work for you.
What is the measure computing?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The measure is calculating from other measure in the table. There are several what if parameters that need to have manual touch
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
09-13-2024 12:38 PM | |||
01-13-2023 01:28 PM | |||
06-01-2022 05:26 AM | |||
03-14-2019 07:43 AM | |||
Anonymous
| 05-22-2024 07:02 AM |
User | Count |
---|---|
111 | |
77 | |
54 | |
53 | |
44 |
User | Count |
---|---|
183 | |
119 | |
77 | |
65 | |
56 |