Skip to main content

I’ve got the following user request but I am not sure what the most optimal solution is, as i can think of many possibilities.

I’ve got 2 Facts Tables:

  • DSA.Fact_Turnover
  • DSA.Fact_TransportOrders

Both Facts contain the ‘Project’ column. The user request is to finally get the Turnover for all projects which are in Fact_TransportOrder. 

What is the most optimal way to get a check column to see if the Project value of Fact_TransportOrder is also present in Fact_Turnover and why? I want to use this filter column to eventually only present him the Turnover for projects which are also present in the Fact_TransportOrder.

I’m really curious, as I already have such solutions, but am not sure if there are more easy TimeXtender functions or tricks which i’m not using currently.

My current solution is as follows:

In my Delivery header table I made a TruckDeliveryCheck column which is Fixed 0, and 1 when the transport mode = Truck

I join this TruckDeliveryCheck column on the Facts_Turnover on Facts_Turnover.Project = DeliveryHeader.Project. This way I can see if the Turnover.project has a Truck Delivery on it.

Is this the most optimal way to achieve this goal?

Hi Kevin

That is how I would have done it as well.

Add a custom field in the source, give it a value when it hits the condition. Add it as a lookup in the other table and you will have a check to make filters on. Instead of adding this field to your semantic table, you could also create a custom yes no or true false table that you then use as a dimension in your semantic model that points at this field.

I just wanted to wait a bit to allow others to come with their suggestion as I am not really using the program in a live setting and others may have had experiences dealing with this.

Not sure what performs best. I usually make a lookup that does a Count from Delivery with join being Project=Project and Transport Mode = ‘Truck’. Then use the Count-field to determine the faith/destination for the fact-record. 

Reply


Terms and ConditionsCookie settings