If function on dataset

[rpinxt]

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?

[rpinxt]

@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 · Accepted solution]

@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]

@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]

@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 🤣

[Anonymous]

@rpinxt ,
Correct me if I am wrong, your condition is 

IF

flow = Reworked  AND Amt LC > -50 
result = Show Quantity Value

Else

result = 0 (false )

[rpinxt]

I was able to solve my problem 😁

I merged the source with the excel file based on 'FlowKey'.

Now I had everything in 1 table. Then with calculated columns it was easy(er) to get the amounts that I wanted.

Thanks for your help

[Anonymous]

@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]

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]

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.

[JorgePinho]

Do you want to create a new column based on that condition?

[rpinxt]

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