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.
Hello,
I'm trying to vlookup one text column "Conca Partner-Loc" on another text column "Conca Loc-Partner" to retrieve corresponding value "Balance"
See below an example :
I want to calculate "Partner balance" column which is simple SUMIF formula in excel (range, Criteria, Sum Range) where Range is "Conca Loc-Partner", Criteria "Conca Partner-Loc" and Sum Range "Balance"
Thank you
Solved! Go to Solution.
@CrisCros23 with DAX measures can be achieved two ways (maybe more I am sure of that)
Measure1 =
CALCULATE (
SUM ( t1[Balance] ),
TREATAS ( VALUES ( t1[Conca Loc-Partner] ), t1[Conca Partner-Loc] ), ALL(t1)
)
Measure2 =
CALCULATE (
SUM ( t1[Balance] ),
FILTER ( ALL ( t1 ), t1[Conca Partner-Loc] = MAX ( t1[Conca Loc-Partner] ) )
)
with PQ you need to self join the table on t1[Conca Loc-Partner] to t1[Conca Partner-Loc]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzKlU0lFyTy3KTcwDsQwNDJRidaKRRIILEjPzgLQRVAbGh+k1BonHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C1 = _t, C2 = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"C1", type text}, {"C2", type text}, {"Value", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"C1"}, #"Changed Type", {"C2"}, "Changed Type", JoinKind.LeftOuter),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Value"}, {"Value.1"})
in
#"Expanded Changed Type"
pbix is attached
@CrisCros23 with DAX measures can be achieved two ways (maybe more I am sure of that)
Measure1 =
CALCULATE (
SUM ( t1[Balance] ),
TREATAS ( VALUES ( t1[Conca Loc-Partner] ), t1[Conca Partner-Loc] ), ALL(t1)
)
Measure2 =
CALCULATE (
SUM ( t1[Balance] ),
FILTER ( ALL ( t1 ), t1[Conca Partner-Loc] = MAX ( t1[Conca Loc-Partner] ) )
)
with PQ you need to self join the table on t1[Conca Loc-Partner] to t1[Conca Partner-Loc]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzKlU0lFyTy3KTcwDsQwNDJRidaKRRIILEjPzgLQRVAbGh+k1BonHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C1 = _t, C2 = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"C1", type text}, {"C2", type text}, {"Value", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"C1"}, #"Changed Type", {"C2"}, "Changed Type", JoinKind.LeftOuter),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Value"}, {"Value.1"})
in
#"Expanded Changed Type"
pbix is attached
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 |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |