Forum Discussion
Help Please.. Custom Colum Filter Value Doesn't Match Measure Value
In Power Query (Transform Data), you can simply go to Transform -> Replace values
You can leave "Value to find" empty and just put 0 in "Replace with"
right the problem is that the sales values always have values in the raw data, they just go as blank on the report when the dates are already filtered in a way that some customers doesn't have any sales for that date range so replacing value from blank to 0 doesn't seem to be applicable on my raw data
Two other things:
1. Why do you go through an intermediate Column step for the status? The way I see it, you do:
- Step 1/ Column 1 : IF the sale is > 0 then "With TPS" else "Without TPS"
- Step 2/ Column 2: If Column 1 is "With TPS" then "With TPS" else "Without TPS"
Why don't you use just one column for the status (as below)?
Status = IF(NetSales_CBL > 0, "With TPS", "Without TPS")This may also get rid of the two errors you have in there.
2. Another way to get rid of the blanks (if you can't touch the imported data) is to create a new column as below:
NetSales_CBL_Forum = IF([NetSales_CBL] <> 0, [NetSales_CBL], 0)Hi MNedix
Really appreciate your help! think we're close and just confirmed my thought that the issue can be with the date but not sure how to work it out...
1. I did this one previously that's why the one I sent has a v2 because this one has the same result (see snapshot below)2. now #2 recommendation, I think it still flagged the customers as with Purchase (shows netsales value) but the problem there is that their NetSales were done not within the filtered date range of Sept 27. If you notice that the first 14 had NetSales_CBL values and their equivalent NetSales_CBL_Forum values, but you have another 2 showing (not a complete list) and they have values in NetSales_CBL_Forum (column) but they don't have a value in the NetSales_CBL (measure)
If it would further help, the Customer list that I placed in there is from a table that's joined to the sales table, where as the sales table has dates of sales, itemname, sales value etc..I think the issue is deeper into the data model, you should not have the two circled values in there and the empty value in between them is also an error which should not appear. It's quite difficult to understand where this is coming from without looking at the model.
What is the NetSales_CBL measure?