1 2 Previous Next 20 Replies Latest reply on Sep 4, 2018 1:11 PM by Deepak Rai

# LOD Expression not working correctly.

I have a Power BI Calculated field

Here is what I have for a LOD expression in Tableau

{FIXED [Origin City State]: SUM(IF {FIXED [Origin City State]:MAX([SalesDeliveryDate])}=[SalesDeliveryDate] THEN [RatePerTon] END)}

I need to get the Most Recient Rate Per Ton based on the Origin City State

What am I doing wrong?

• ###### 1. Re: LOD Expression not working correctly.

Hi Stephen,

Could provide more context to this? Ideally, a sample workbook would do.

Emmanuel.

• ###### 2. Re: LOD Expression not working correctly.

HI Stephen,

I tried replicating the formula using superstore data:

max value  = {FIXED [Sub-Category]:SUM(IF {FIXED [Sub-Category]:MAX([Order Date])}=[Order Date] THEN [Sales] END)}

• ###### 3. Re: LOD Expression not working correctly.

I have included the sample workbook on the post

• ###### 4. Re: LOD Expression not working correctly.

I already have a calc like this but it's not working.  The one you posted for the example is the same result's I'm getting which are not correct.  It's also based on Max(SalesOrderDate) for most recient order date.

• ###### 5. Re: LOD Expression not working correctly.

Hi Stephen,

Looking through the sample workbook you provided, I see why the LOD is not working as intended. Some cities (e.g. Atchison, KS) have more than one RatePerTon for the latest date.

What your LOD does is to sum these values i.e. Atchison, KS would give you 25+29+45+52 = 151 for 31/08/2018.

Hence, you cannot get "Most Recient Rate Per Ton based on the Origin City State" since there are multiple values on same date level. What you could do is calculate the average, which I'm not sure is what you are after.

Emmanuel.

• ###### 6. Re: LOD Expression not working correctly.

I'll take a look at that.  The Original Power BI  calc was Most Recent Rate = CALCULATE( MAX(LoadRelationship[RatePerTon]), FILTER(LoadRelationship, LoadRelationship[SalesDeliveryDate]=MAX(LoadRelationship[SalesDeliveryDate]))) so I'm not sure what PowerBI is doing with the data.

Thanks for look at this,

• ###### 7. Re: LOD Expression not working correctly.

Hi Stephen,

Try This:

{Fixed Sales Delivery Date,Origin City State: MAX(Rate per Ton)}=Rate per Ton

Drag it to Filter and Select TRUE

Thanks

Deepak

• ###### 8. Re: LOD Expression not working correctly.

If Power BI is connected to same data as Tableau, I'd recommend you have a look what Power BI is doing to make sure you are getting the right answers, which I doubt (considering the data's structure).

• ###### 9. Re: LOD Expression not working correctly.

Emmanuel, I think I have found what I need help with.  The calculation in PowerBI is looking at the Greatest Delievery date and using the RatePerTon not the average so if I have

Rate               SalesDelieveryDate

42.00              2017-04-10

42.00              2017-03-13

PowerBI is showing the Rate based on the Latest SalesDelieveryDate.  How would I be able to do that in tableau?

• ###### 10. Re: LOD Expression not working correctly.

{FIXED : MAX({Sales Delivery Date)}=Fixed Sales Delivery Date

Drag to Filter and Set TRUE

• ###### 11. Re: LOD Expression not working correctly.

Having had a closer look, the Power BI calculation gives you the maximum Rate Per ton for the most recent Sales Delivery Date.

To do that, use the calculation below if you want the maximum Rate Per Ton across all Origin Cities:

{MAX(IF {FIXED [Origin City State]:MAX([SalesDeliveryDate])}=[SalesDeliveryDate] THEN [RatePerTon] END)}

or this if you want the maximum Rate Per Ton for each Origin City:

{FIXED [Origin City State]: MAX(IF {FIXED [Origin City State]:MAX([SalesDeliveryDate])}=[SalesDeliveryDate] THEN [RatePerTon] END)}

I hope this helps.

Emmanuel.

• ###### 12. Re: LOD Expression not working correctly.

Try this for each City

{FIXED Origin City : MAX({Sales Delivery Date)}=Sales Delivery Date

Now In your view drag It to Filter and Set TRUE to get per rate Per Tonne  per Origin City, Of course you need to drag Rate per ton to Column

• ###### 13. Re: LOD Expression not working correctly.

The calculation did not work I got the following error

Expected '}' following the expression.  I change it to this due to what the name of the fields were.

{FIXED [Origin City] : MAX({[SalesDeliveryDate])}=[SalesDeliveryDate]

I'm needing to get the following The RatePerTon based on the Value of the [OriginCityState] and the MAX([SalesDeliveryDate}) for that OriginCityState  Does that make since?

So if the data looked like this

OriginCityState      SalesDeliveryDate     RatePerTon     .....

Paris, IL                  2017-03-13               43.00

Paris, IL                  2017-04-10               42.00

.....                         .........                         ..........

(there are only 2 in the DB with this information)

I would like the results to look like this:

OriginCityState     MostRecientDeliveryDate     RatePerTon(AKA Most Recent Rate)

Paris, IL               2017-04-10                           42.00

Cloverdale, IN      2016-08-17                           40.00

I'm using Tableau Desktop 2018.2

• ###### 14. Re: LOD Expression not working correctly.

Here It Is:

Filter to Get the above View:

Thanks

Deepak

If it Helps, Pl mark It Helpful and CORRECT to Close Thread

1 2 Previous Next