Forum Discussion
Power ParticipantConvert a multiple IF formula from DAX to M
Hi folks,
I am terrible at M and I really need this formula in the Power Query so that I can then unpivot the table. Could anybody help me, please?
2019-01 =
SWITCH('Business'[Line],
"Lyme",
if(AND(related(Business[Close Date])<=date(2019,01,31),related(Business[Close Date])>=date(2019,01,01)),
[Amount]),
"Mountain",
if(and(related(Business[Start Date])<=date(2019,01,31),related(Business[End Date])>=date(2019,01,01)),
divide('Row'[Amount],'Row'[Term]))
"Earth",
if(and(related(Business[Start Date])<=date(2019,01,31),related(Business[Start Date])>=date(2019,01,01)),
('Row'[Amount]*0.6),
if(and(related(Opp[Start Date])<=date(2019,01,31),related(Opp[End Date])>=date(2019,01,01)),
divide('Row'[Amount]*0.4,'Row'[Term])))
)
If 'related' doesn't work, I can easily calculate these columns in the 'Row' table.
A big thank you.
Alice
3 Replies
- lbendlin
Super User
M is much simpler than DAX in this respect. All it has is and and or. No fancy switch syntax sugar.
Keep in mind that in Power Query your tables are not yet related. You either need to merge tables, or use custom column generator functions to lookup values in other tables.
Best would be to provide sample data for your row data source and business data source.
Hi AliceW ,
See if these blogs help you
https://www.youtube.com/watch?v=ivNtaAl7XNM
https://www.poweredsolutions.co/2019/06/09/logical-operators-and-nested-ifs-in-power-bi-power-query/
https://www.myonlinetraininghub.com/power-query-if-statements
https://exceloffthegrid.com/power-query-if-statements-for-conditional-logic/
https://www.excelguru.ca/blog/2014/08/27/the-if-function-in-power-query/
Regards,
Harsh Nathani
- v-diye-msft
Community Support
