Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to implement the below dax in power query

I am fairly new to power query,
 
The below logic is of a calculated column, how to implement the same in power query:
 
Flag =
IF (
'Discoverer- AP Payment 1 - Summary'[Difference] = 0,
"Matched",
IF (
AND (
OR (
'Discoverer- AP Payment 1 - Summary'[Discoverer Amount] = BLANK (),
ROUND ( 'Discoverer- AP Payment 1 - Summary'[Discoverer Amount], 0 ) = 0
),
'Discoverer- AP Payment 1 - Summary'[Difference] <> 0
),
"Ignore",
IF (
AND (
'Discoverer- AP Payment 1 - Summary'[AP Amount] = 0,
ROUND ( 'Discoverer- AP Payment 1 - Summary'[Discoverer Amount], 0 ) <> 0
),
"Void/Unmatched",
"Unmatched"
)
)
)
Need Help
  • ERD's avatar
    ERD
    5 years ago

    Hi Anonymous ,

    What error do you get? Make sure you choose correct types for your columns (Decimal numbers for DA,Difference,AP).

    if [Difference] = 0 
then "Matched"
else if ([DA]=null or Number.Round([DA],0) = 0) and [Difference] <> 0 
then "Ignore" 
else if [#"AP "] = 0 and Number.Round([DA],0) <> 0 
then "Void/Unmatched" 
else "Unmatched"

6 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    first I see you have measure inside that dax, Power Query doesnt use measure you need to break those down to it logical code, its pretty much the same except for the functions name but basically copy that code, delete all parentesis and comas for the ifs, the logic of if its as follow 

     

    if Column 1 > column 2 and column 2 > 3   then 1 else 2    --> if you want to imput text its the same use "1" intead of number 1 it will insert the value as a text for thta column. you cant mix datatype on the results. 

    equivalent of blank() --> null 

    round its --> number.round

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    SOMETHING LIKE THIS (REMEBER YOU NEED TO BREAK DOWN THE MEASURE HERE FOR IT TO WORK ALSO): 

    IF

    (Discoverer- AP Payment 1 - Summary'[Difference]) = 0 then

    "Matched",

    Else IF

     

    ('Discoverer- AP Payment 1 - Summary'[Discoverer Amount] = null OR

    Number.ROUND( 'Discoverer- AP Payment 1 - Summary'[Discoverer Amount], 0 ) = 0) AND

    'Discoverer- AP Payment 1 - Summary'[Difference] <> 0

     

    Then

    "Ignore",

    Else IF 

    ('Discoverer- AP Payment 1 - Summary'[AP Amount] = 0 AND

    NUMBER.ROUND ( 'Discoverer- AP Payment 1 - Summary'[Discoverer Amount], 0 ) <> 0) THEN

    "Void/Unmatched" ELSE "Unmatched"

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I have tried this but getting error, not able to understand what is wrong:

       

      if

      [Difference] = 0 then

      "Matched"

      else if

      [Discoverer Amount] = null OR

      Number.ROUND( [Discoverer Amount], 0 ) = 0) AND

      [Difference] <> 0

      Then

      "Ignore",

      Else IF

      ([AP Amount] = 0 AND

      NUMBER.ROUND ( [Discoverer Amount], 0 ) <> 0) THEN

      "Void/Unmatched" ELSE "Unmatched"

      • StefanoGrimaldi's avatar
        StefanoGrimaldi
        Resident Rockstar
        5 years ago

        try this: 

        if

        [Difference] = 0 then

        "Matched"

        else if

        ([Discoverer Amount] = null) OR

        (Number.ROUND( [Discoverer Amount], 0 ) = 0 AND

        [Difference] <> 0)

        Then

        "Ignore",

        Else IF

        ([AP Amount] = 0 AND

        NUMBER.ROUND ( [Discoverer Amount], 0 )) <> 0 THEN

        "Void/Unmatched" ELSE "Unmatched"

         

        if doesnt work send a sample data or a sample PBIX to fix it and give the exact Mquery formula.