Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
3 years ago
Solved

If function on dataset

I have this data set: In this "Flow" comes from an excel file and the rest from 1 other data source.   Now I want to do an excel like funtion like:   If 'Flow' = "Reworked" then   TRUE  -...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    rpinxt ,
    If the two tables are related to each other this will work.

     

    Measure =
    Var a = MAX(Sheet1[Flow]) = "Reworked"
    var b = MAX(Sheet1[Amt LC]) < -50
    var c = a && b
    var d = IF(c,MAX('Sheet1 (2)'[Quantity]), 0)
    return d

     

    Regards,

    Ashfiya

    --------------------------------------------------------------------------------------------------------------------------

    Did I help you today? Please mark my post as a solution and hit the Kudos button.

     

rpinxt's avatar
rpinxt
Solution Sage
3 years ago

Anonymous  well not understanding your logic fully.

Tried this :

 

First of I do not understand why you reference Flow by using a MAX?

From table 'AVN Rework Flow' I want to check if the value is "Reworked"

And from table 'AVN Rework' I want to check if the amount is more/less then  - 50

 

If these are both true then I want to show the quantity.

And if it is false I want to show 0 (quantity).

 

As you see now the true and false are all over the place.

Reworked shows false but also Other shows false.

And x shows true (guessing with the max you where thinking that Reworked would be the word highest in alphabet?)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    rpinxt 
    Since it's a row-by-row operation in the measure, irrespective of any aggregation ( min or max ) it will take the value itself. Since you mentioned "Flow" and my "Quantity" come from 2 different tables.  How are these tables related to each other? Do you have any primary column?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    rpinxt ,
    If the two tables are related to each other this will work.

     

    Measure =
    Var a = MAX(Sheet1[Flow]) = "Reworked"
    var b = MAX(Sheet1[Amt LC]) < -50
    var c = a && b
    var d = IF(c,MAX('Sheet1 (2)'[Quantity]), 0)
    return d

     

    Regards,

    Ashfiya

    --------------------------------------------------------------------------------------------------------------------------

    Did I help you today? Please mark my post as a solution and hit the Kudos button.

     

    • rpinxt's avatar
      rpinxt
      Solution Sage
      3 years ago

      Anonymous thanks this is apparently working:

      Now I have only output for the Flow Reworked.

      Still have to study the code to grasp why the (excel) vlookup is done with Max but it is working.

       

      Those 2 tables are linked together with 'FlowKey'.

      Above you see this key. This is present in both tables.

      Thanks for the help!

      • rpinxt's avatar
        rpinxt
        Solution Sage
        3 years ago

        Anonymous  aiii I spoke to soon....

         

        I put in some more detail and now it is not working anymore.

        I had put in example on a higher level to make it better readable.

         

        But now I only put in 1 extra field :

        As you see the yellow lines are ok as the amount is less then -50
        But the red line it is -42  which is more then -50 so that should not be 0

         

        **EDIT**: ohhh never mind...complete losing the overview here....

        Its -42.000 which clearly is more than -50....😖
        Will experiment so more.

        Maybe I do have to do a calculated colum as I notice it is really laggy because it needs to calculate each row.

        Not sure if that will work with fields from 2 tables.

        Maybe have to do a union if that is possible.....

         

        Lots to experiment 🤣