Analysis Services tabular Measure question

  • I am trying to create an on-time delivery measure for a data warehouse I am building, but the criteria for on time changes based on the subsidiary as well as the customer. For Example, for some customers you can be 5 days early or late, but for others, you can only be 1 day late or early.  I had the idea to create a table called daysontime that has 3 integer values - Days, Early & Late.  The Late values are negative, the early days are positive and the days column would also be positive. Then I have a field for the shipment date & A field for the promised date. I created a calculated column for the promise date - ship date difference called promdatediff. My initial thought was to create a pair of measures called DaysEarly & Dayslate that use MAX() and MIN() on the early & Late columns, then use the measures in a calculated column called on time so that I could calculate the On time percentage based on quantity of items, orders or dollar amount from there.  however, it appears that the calculated column is not effected by the measure changing when using the slicer in Power BI .  Is there something I can use in the formula for the column to have it re-calculate the column when the filters of the measure change, or will I have to create measures for each type of on time delivery.

  • Can't you just use CALCULATE() and IF/SWITCH to handle the different options? CALCULATE lets you override some or all of the current evaluation context. Wouldn't the difference between "early" and "late" be that one is positive and the other is negative?

  • The difference would be positive vs negative.  The issue I am running into is that I am wanting to have an on time status for each individual record, not just at an aggregate level.

  • If you want it to change in context, then it needs to be done in the measure. Calculated columns are done at processing time and don't change. Take a look at the "X" functions...(SUMX, COUNTX, MAXX, etc). They may help because you could calculate for each customer. If these could work, try and understand the performance issues around them also if your data is even slightly large.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply