Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all
I have issues getting the right query for the scenario below. I appreciate it´s rather complicated to understand, but let me give it a try.
Table Name: Register
Credit Number | Currency | Credit |
1ABC | EUR | 2,000 |
1ABC | USD | 25,000 |
1ABC | HKD | 50,000 |
2HIJ | GBP | 35,000 |
3KLM | HKD | 125,000 |
Conditions:
If 1 Credit Number has a Currency = EUR && a Currency = USD,
and if Credit for EUR is smaller than Credit for USD,
then I want to keep Credit for EUR and make Credit for USD as 0,
but if Credit for EUR is bigger than Credit for USD, I want to make both Credit as 0
if none of the above, keep Credit as is
Expected Outcome:
Credit Number | Currency | Credit |
1ABC | EUR | 2,000 |
1ABC | USD | 0 |
1ABC | HKD | 50,000 |
2HIJ | GBP | 35,000 |
3KLM | HKD | 125,000 |
Appreciate your thoughts!
Solved! Go to Solution.
@MikeFo , Create a new column
new Credit =
Var _Eur = maxx(filter(Table, [Credit Number]=earlier( [Credit Number]) && [Currency]= "EUR"), [Credit])
Var _USD = maxx(filter(Table, [Credit Number]=earlier( [Credit Number]) && [Currency]= "USD"), [Credit])
Switch( True(),
_Eur > _USD && [Currency] in {"EUR", "USD"}, 0,
_Eur < _USD && [Currency] in {"USD"}, 0,
[Credit])
@MikeFo , Create a new column
new Credit =
Var _Eur = maxx(filter(Table, [Credit Number]=earlier( [Credit Number]) && [Currency]= "EUR"), [Credit])
Var _USD = maxx(filter(Table, [Credit Number]=earlier( [Credit Number]) && [Currency]= "USD"), [Credit])
Switch( True(),
_Eur > _USD && [Currency] in {"EUR", "USD"}, 0,
_Eur < _USD && [Currency] in {"USD"}, 0,
[Credit])
Thanks, this helped.
How would I apply this in the "Transform Data" view i.e. before the Close & Apply step?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
65 | |
63 | |
56 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |