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  - If  ('Amt LC' > -50 ; 'Quantity' ; 0)

  FALSE -  0

 

But I am having a hard time getting this (what I think) easy calculation.

Could somebody put me on my way?

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.

     

11 Replies

    • rpinxt's avatar
      rpinxt
      Solution Sage
      3 years ago

      No I do not want a new column as the data involved comes from 2 different tables in my data sources.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi rpinxt ,
    I tried this measure as per my understanding. [TRUE  - If  ('Amt LC' > -50 ; 'Quantity' ; 0) ] I did not understand the 2nd condition for Quantity. Please check the below measure.

    Measure =
    Var a = Not(MAX(Sheet1[Flow])) = "Reworked"
    var b = MAX(Sheet1[Amt LC]) > -50
    var c = a && b
    return if(c, "0","(true value)")

     

    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

      Ok this looks like it could work but my "Flow" and my "Quantity" come from 2 different tables.

      Here you have them both in 'Sheet1'.

      I think that is were my problem is.

  • 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!