In this case the order from Best to Worst is:
- M custom Column
- DAX Calculated Column
But the only right choice is number 1, the Measure. That doesn’t need to be stored in your Model, will only get evaluated when used in your Report and more importantly it’s fully dynamic when it comes to the filter context thus allowing you several different insights with just one calculation.
A Custom column / Calculated column don’t share that capability, once calculated they are fixed.
There are a couple of ways to deal with your “Starts with” scenario but I like to use secondary table logic because that’s easy to maintain. Here’s my SalesAdjustment table:
I created that by going to “Enter Data” on the Home tab and manually typing it in but there are other ways to create a table like this of course.
You already have a [Total Sales] measure so I used measure branching to reference that:
Adjusted Sales =
SUMMARIZE( Sales, Sales[Warehouse Code], Dates[Date],
"AdjSales", [Total Sales] * COALESCE( CALCULATE( VALUES( SalesAdjustment[Adjustment] ), FILTER( SalesAdjustment, SalesAdjustment[Name start] = LEFT( SELECTEDVALUE( Sales[Warehouse Code] ), 3) )), 1)),
Couple of things happening here:
- SUMX we need an iterator to also calculate the Adjusted Sales for the Total
- SUMMARIZE creates the table to iterate over
- Next for each Sales[Warehouse Code] we’re going to FILTER the SalesAdjustment table to check if the first 3 characters are in the SalesAdjustment[Name start] column. If that is TRUE then it will return a single value from the SalesAdjustment[Adjustment] column. But if there is no match FILTER will return an empty table so in that case COALESCE will multiply by 1 otherwise the result would be a BLANK.
Here is the result.
I hope this is helpful. Here’s my sample file.
eDNA - Adjusted Sales.pbix (450.5 KB)
Alternatively you could also explore SWITCH TRUE logic.