Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there...
I have been struggling a bit with something I think should be simple so I thought I would ask here.
I have two queries....
Table1 looks like this
Table 2 like this
I was trying to create a new column in Table 2 called Result which was based on the following logic:
IF 'Table1'[Credit ($)] > 'Table2'[Total ($)], return 'Table2'[Total ($)],else return 'Table1'[Credit ($)]
This was easily accomplished by merging queries placing all of the fields in one query.
Result = IF([Credit ($)]>[Total ($)],[Total ($)],Credit ($)])
What I am interested in knowing is how could I accomplish this without merging queries.
Is there a way to do this in power query?
Is there a way in DAX?
I played around some with RELATED and RELATED TABLE as these queries share a relationshiop (The client field) but was unsuccesful.
Any assitance in a non merge queries solution would be appreciated.
Thanks
Kyle
Solved! Go to Solution.
You were on the right track with RELATED. In the example, the tables have a 1:1 relationship:
Calculated column in Table 2:
Result =
VAR vTable1Credit =
RELATED ( Table1[Credit ($)] )
VAR vResult =
IF ( vTable1Credit > Table2[Total ($)], Table2[Total ($)], vTable1Credit )
RETURN
vResult
Result:
Proud to be a Super User!
You were on the right track with RELATED. In the example, the tables have a 1:1 relationship:
Calculated column in Table 2:
Result =
VAR vTable1Credit =
RELATED ( Table1[Credit ($)] )
VAR vResult =
IF ( vTable1Credit > Table2[Total ($)], Table2[Total ($)], vTable1Credit )
RETURN
vResult
Result:
Proud to be a Super User!
Thanks. That did the trick!... I can't thank you enough as this was driving me crazy.
I noticed that this is not working if I have a one to many relationship between the tables,. Is there something I would do different in a one to many relationship situation?
Glad to hear that works. In a one-to-many relationship, you would use RELATEDTABLE and use an aggregate function since multiple rows would be returned on the many side. Which table is on the many side?
Proud to be a Super User!
Hi there,
Table 1 is one the many side (although my sample data does not show it). When aggregating and using RELATED TABLE will I be able to isoloate rows to be displayed.
For example, table one might have three client Kyle rows with three different credit amounts ($45, $50, $125) but I want to display only one them based on some critera.
You have been very helpful already so no problem if you dont have time to respond. I will certainly do some additional research into this.
Thanks again!
Kyle
Here's an example with a one-to-many relationship, in which you want the amount where Flag = 1.
Table1:
Table2:
Calculated column:
Result =
VAR vTable1Credit =
CALCULATE ( MAX ( Table1[Credit ($)] ), Table1[Flag] = 1 )
VAR vResult =
IF ( vTable1Credit > Table2[Total ($)], Table2[Total ($)], vTable1Credit )
RETURN
vResult
Proud to be a Super User!
Thank you!
Hi,
Share relevant/representative data in both tables and show the expected result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |