Forum Discussion
Change DAX measure into Power Query Editor Custom Column
Dear Comunnity,
I have a DAX measure as below, and would like to change it into power query custom column. I wonder isit able to do that, because if I create a measure with DAX, I unable to use the merge queries feature in Power Query Editor.
In this case, without using the merge queries, I have to use Crossjoin in DAX which will consume a lots of time when I refresh the report.
DAX need change to power query
Unique Match Column =
var consignee=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignor=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignee1=CALCULATE(Max(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber])
var consignor1=CALCULATE(Max(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var match=SWITCH(TRUE(),
AND(consignor=1,consignee=1),consignee1,
consignee=1,consignee1,
consignor=1,consignor1,
AND(ISBLANK(consignor),consignee<>1),"BLANK",
AND(ISBLANK(consignee),consignor<>1),"BLANK",
"MIX")
return match
Hope you guys can give me some advice.
Attached with the pbix: https://ufile.io/5l2rjw9a
Greatly appreciate any help and thanks for your attention.
11 Replies
DAX measures cannot be rewritten in Power Query because they are computed dynamically based on user filter selections.
Your item above seems to be a calculated column (not a measure) so it can be converted to Power Query. However your sample PBIX (thank you for posting it) points to a local file data source
'C:\Users\nikchoo\Desktop\Testingconsignornee.xlsx'
that we of course don't have access to. Please post the sample data as well.
Dear lbendlin,
Thank you so much for your attention.
My apologies. Here's the pbix and excel file: https://ufile.io/q2pty4w9
Greatly appreciated.
Dear lbendlin ,
Good day!
At your convenience would you mind have an update on this please?
Much appreciated.
var consignee1=CALCULATE(Max(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber])This would sort Consignee (and Consignor) alphabetically. Is that what you are intending?
Dear lbendlin ,
Thank for the reply. Yes correct.
Here's the result when consignee or consignor condition have been meet.
If there is more than one brands in Consignee/Consignor column, there will have result as 'MIX'.
So doesnt matter if is sort in aplhabetically.
Thank you
Hi,
Please explain the logic. For C001, why is the answer NIKE and not ADIDAS? Please also explain the logic of the other ConsolNumbers.
