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

LOD Expression not working correctly.

Stephen Crocker Sep 4, 2018 1:01 PM

I have a Power BI Calculated field

Most Recent Rate = CALCULATE( MAX(LoadRelationship[RatePerTon]), FILTER(LoadRelationship, LoadRelationship[SalesDeliveryDate]=MAX(LoadRelationship[SalesDeliveryDate]))) that I want to convert to a tableau LOD Expression.

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?

Book1.xlsx 10.2 MB
Sample.twbx 358.2 KB

1. Re: LOD Expression not working correctly.

Emmanuel Ikehi Sep 4, 2018 8:47 AM (in response to Stephen Crocker)

Hi Stephen,

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

Emmanuel.
Like Show 0 Likes(0)

Actions
2. Re: LOD Expression not working correctly.

meenu choudhary Sep 4, 2018 8:50 AM (in response to Stephen Crocker)

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)}

Can you please explain with an example your expected output??
Like Show 0 Likes(0)

Actions
3. Re: LOD Expression not working correctly.

Stephen Crocker Sep 4, 2018 9:05 AM (in response to Emmanuel Ikehi)

I have included the sample workbook on the post
Like Show 0 Likes(0)

Actions
4. Re: LOD Expression not working correctly.

Stephen Crocker Sep 4, 2018 9:07 AM (in response to meenu choudhary)

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.
Like Show 0 Likes(0)

Actions
5. Re: LOD Expression not working correctly.

Emmanuel Ikehi Sep 4, 2018 11:15 AM (in response to Stephen Crocker)

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.

I hope this is helpful.

Emmanuel.
Like Show 0 Likes(0)

Actions
6. Re: LOD Expression not working correctly.

Stephen Crocker Sep 4, 2018 11:11 AM (in response to Emmanuel Ikehi)

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,
Like Show 0 Likes(0)

Actions
7. Re: LOD Expression not working correctly.

Deepak Rai Sep 4, 2018 11:18 AM (in response to Stephen Crocker)

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
Like Show 0 Likes(0)

Actions
8. Re: LOD Expression not working correctly.

Emmanuel Ikehi Sep 4, 2018 11:22 AM (in response to Stephen Crocker)

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).
Like Show 0 Likes(0)

Actions
9. Re: LOD Expression not working correctly.

Stephen Crocker Sep 4, 2018 11:21 AM (in response to Emmanuel Ikehi)

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?
Like Show 0 Likes(0)

Actions
10. Re: LOD Expression not working correctly.

Deepak Rai Sep 4, 2018 11:26 AM (in response to Stephen Crocker)

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

Drag to Filter and Set TRUE
Like Show 0 Likes(0)

Actions
11. Re: LOD Expression not working correctly.

Emmanuel Ikehi Sep 4, 2018 11:35 AM (in response to Stephen Crocker)

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.
Like Show 0 Likes(0)

Actions
12. Re: LOD Expression not working correctly.

Deepak Rai Sep 4, 2018 11:39 AM (in response to Stephen Crocker)

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
Like Show 0 Likes(0)

Actions
13. Re: LOD Expression not working correctly.

Stephen Crocker Sep 4, 2018 12:34 PM (in response to Deepak Rai)

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
Like Show 0 Likes(0)

Actions
14. Re: LOD Expression not working correctly.

Deepak Rai Sep 4, 2018 12:45 PM (in response to Stephen Crocker)
Here It Is:
Filter to Get the above View:

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

Max.twbx 11.7 KB
Like Show 0 Likes(0)

Actions