Working with a measure i encountered a mishap in how to treat blank values as zeroes, I was able to work around the issue but how some intermediate solutions interacted with filters baffled me, so I was hoping some of you could help clear things up.
I intended to treat blank values as zeros when it's evaluated in a table listing all of the Fact_table rows.
The setup is you have a fact table and a dimension table:
Fact_table:
Fact_ID,Value,Dim_Id
1,34,2
2,15,1
3,,2
4,,2
5,10,2
Dim_table:
Dim_ID,Category
1,"cat1"
2,"cat2"
two simple measures:
Sum Value := SUM(Fact_table[Value])
Sum Value No Blanks := SUM(Fact_table[Value]) + 0
the sample model:
When you use [Sum Value] in a table listing all Fact_Id, the Ids that make the measure evaluate to blank are omitted(which is expected):
When you use [Sum Value No Blanks] in the same table, all the Ids are listed now(which is expected):
Now when you filter by the dimension table by the cat2 this is how they behave:
Fact_Id 2 still is included in the table visual despite it being a row of category cat1(which shouldn't be included), I understand the stance that it is been shown because the measure is evaluating to a non-blank value however when you filter by the Dim_id column in the Fact_table the result is the following:
Now row 2 of Fact_table is no longer being included despite the measure not being evaluated to blank anymore.
I understand that the two contexts should be equivalent yet they aren't.
my first intuition was that it was something to do with the table visual and that it treats filters affecting table columns included in the table directly differently from those coming from filters in other tables and being passed along the relationship but when you include the Category column from the Dim_table it seems to behave in the same manner.
do you have any explanations for the difference in behavior in what seem to be equivalent filter contexts?
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.
sample report: https://we.tl/t-kvoC6ACUMj
the expected result is for the filter on the dimension table and the filter on the column from the fact table to produce the same result
