Forum Discussion

M_OConnor's avatar
M_OConnor
Icon for Helper I rankHelper I
6 years ago
Solved

Avoiding 'zero' values in a DAX when you have multiple selected in a filter

Let's say I want to show a bar graph showing Year on Year gross profit for stores broken down into speciic products.   Here's an example of some data I have for three stores. Let's say they sell ch...
Need Help
Tips and Tricks
  • smarthp29's avatar
    smarthp29
    6 years ago

    M_OConnor 

    What if you created another Gross Profit column which only has value if GP Last Year <>0

    IF GP_LY = 0 then 0 else GP

     

    So your 3rd column would be
    Product          GP_Calculation 

    Chainsaw                    0

    Chainsaw                    417

    Chainsaw                    0

     

    And then you this column for calculation

smarthp29's avatar
smarthp29
Icon for Helper I rankHelper I
6 years ago

M_OConnor 

What if you created another Gross Profit column which only has value if GP Last Year <>0

IF GP_LY = 0 then 0 else GP

 

So your 3rd column would be
Product          GP_Calculation 

Chainsaw                    0

Chainsaw                    417

Chainsaw                    0

 

And then you this column for calculation

M_OConnor's avatar
M_OConnor
Icon for Helper I rankHelper I
6 years ago

smarthp29  Cheers, I'll try that and use that exclusively when I'm calculating YoY. Will let you know.

 

Would this column be something in the data source layer or a dax column? This would be fine as it's not the final calculation.

 

Cheers

  • smarthp29's avatar
    smarthp29
    Icon for Helper I rankHelper I
    6 years ago

    It would depend on how your data gets refreshed. 

    For example, if you get post dated transactions then I would suggest a DAX Calculated Column. If not then getting the column configured in the datasource is always the best option. In case you are unsure, DAX Calculated Column shoudl be implemented to ensure you future proof it.