Forum Discussion
using a SUM and ISBLANK includes filtered data
Hey guys,
I have a model with two tables 1 fact (Transactions) and 1 dimension (Seller). There is a one to many relationship from seller to transactions. I have a report level filter on the seller so that I can view transaction data for a single seller.
I first created a measure TotalTransacting = SUM(Transacting). However, I wanted to have default values of "0" where there was no data to display to avoid gaps in things like date ranges. So I tried to modify the query in two ways both are giving me issues.
TotalTransacting = SUM(Transacting) + 0 TotalTransacting = IF(ISBLANK(SUM(Transacting)),0,SUM(Transacting))
The problem I am seeing is that when i use these measures the filter on sellerKey seems to get ignored and I am getting data for all sellers.
Here is a screenshot to help visualize:
You can see that when i drag the sellerkey from the seller table into a table visual it is filtered properly to the right seller. But the seller in the transaction table is not getting filtered. As soon as I change the measure back to a simple SUM the sellers are filtered out but again this is not ideal for my situation as i want the default to be a zero and not blank while still respecting any report filters I have in place. So in the case of the visual above i would expect 1 row with the seller I'm filtered to in the report with a "0" value for my measure. How can I accomplish this?
Anonymous,
I have test it with my sample data, we cannot reproduce this issue. Please note that, you need to use seller column in Seller table in your visual and then enable "Show items with no data".
Measure = SUM(Transacting[Transacting])+0Regards,
Charlie Liao
2 Replies
- v-caliao-msftMicrosoft Employee
Anonymous,
I have test it with my sample data, we cannot reproduce this issue. Please note that, you need to use seller column in Seller table in your visual and then enable "Show items with no data".
Measure = SUM(Transacting[Transacting])+0Regards,
Charlie Liao
- AnonymousNot applicable
You are correct I apologize, I was using the seller in the fact table rather than the seller table. It is interesting to me though as I would have assumed that whether you are using the seller in the seller table or the fact table I would hav expected the same results but it seems using the seller in the fact table brings back all sellers even though there is a filter on the seller.seller column.