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 Everyone,
It is hard for me to calculate like this.
Any help is appreciable. Thank you in advance!
Regards,
Kelly
Solved! Go to Solution.
Without calculation it won't be possble.
Possibly you want to know how to do it in Power Query:
let Source = Query2, #"Merged Queries" = Table.NestedJoin(Source,{"First Level Group", "Second Level Group"},Query1,{"First Level Group", "Second Level Group"},"Query1",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom value", each [Reference] * Table.RowCount([Query1]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reference", "Query1"}) in #"Removed Columns"
Hi @Kelly_Zhu
Add this calculated column in Query 2
Custom Value= Query2[Reference] * CALCULATE ( COUNTROWS ( Query1 ), FILTER ( Query1, Query1[First Level Group] = Query2[First Level Group] && Query1[Second Level Group] = Query2[Second Level Group] ) )
Thanks a lot!
In addition, Could you tell me how to get the data without calcuation? Thanks in advance!
Without calculation it won't be possble.
Possibly you want to know how to do it in Power Query:
let Source = Query2, #"Merged Queries" = Table.NestedJoin(Source,{"First Level Group", "Second Level Group"},Query1,{"First Level Group", "Second Level Group"},"Query1",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom value", each [Reference] * Table.RowCount([Query1]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reference", "Query1"}) in #"Removed Columns"
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |