Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How to handle data that does not exist. When filtering column on 0.00 it includes "blanks" as well

I am working with some vehicle insurance data that unfortunately I cannot share, but hoping I can explain the issue and someone can help anyway.

 

I have a table with many columns relating to vehicles like Make, Model, VIN, Annual Estimated Miles. We recently added deductible fields to our tabular model and I have pulled those in to the table as well. The issue I have run into is when the deductibles do not exist, as in they do not exist at all in any of the layers prior to the tabular model. So not in the lake, data warehouse, mart or view the tabular is built off of. The vehicle itself exists and thus is a row in the table, but for those vehicles where the deductibles do not exist it shows a blank, which would be fine, but what we have found is when we filter the column to $0.00 it is filtering to those rows with a deductible of $0.00 but is also including the rows where the deductible does not exist.

 

I tried creating a calulated column with the code If(ISBLANK(Deductible), 999, Deductible) but this does not work. My assumption is because the data legit does not exist, power bi doesn't actually see it as a true blank and thus cannot address it. I am working with my data engineering team to address the data that does not exist, but I am not sure if they will be able to fix all cases of missing data. Is there any way to stop Power BI from including the missing data rows when filtering on $0.00?

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    when you say the data does not exist, does it show as a null in powerquery? (see sample table)

    I am not quite sure what all kind of filtering you want to do but with strict equal to (==)  it works fine, 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I'm used a mixed mode connection, Live to the tabular import for some SQL stuff. I think with the live connection I am unable to view the tabular model in Power Query, correct me if I am wrong.

       

      Our tabular model is populated from SQL views. When I say the data does not exist I mean there is no row for these paticular vehicles on the deductible view at all. I'm assuming PowerBI can't even address these fields because they aren't nulls at the source, they don't exist at all.

       

      Just noticed, even for those that have a row on the deductible table, if they don't have a certain deductible that value is Null in the view, but still gets included when I filter that deductible field to $0.00 in Power BI. Those that are nulls in the view I can address and change to something else in Power BI if needed, but is that normal behavior for Power BI to include nulls/blanks? when filtering on $0.00?

       

      Found this article, so yeah I guess Dax and thus I assume, filtering, treats blanks as 0

      How DAX Treats Nulls & Zeros | Burningsuit