Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

If Statement creates repeating rows for data that doesnt exist

Hello All, 

 

When I try to compare a sum with another measure, I get repeating False values using an if statement.

 

Within threshold =
var threshold = round(CALCULATE(SUM('Stock'[DC QOH])) * 1.2, 0)


RETURN
if(CALCULATE(SUM('Stock'[DC QOH]) ) < thresh , TRUE() )

 

DC QOH  is a value in the stock table

 

Treshold = round(CALCULATE(SUM('Stock'[DC QOH])) * 1.2, 0)

 

 

 

 

There are 3 tables: 1 fact table 'stock', 1 dim table 'location', 1 dim table 'item' 

 

How do I fix my dax statement remove the repeating irrelevant rows?

 

 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hello, Thank you for your help. I was able to solve my problem using another method. The issue due to 2 dim table columns being used in the matrix. 

    I used this video: https://www.youtube.com/watch?v=3vcKgN8HxjY

     

    and 

    IF(COUNTROWS('(Live) Store Stock') = 1,if(CALCULATE(SUM('Stock'[DC QOH]) ) < thresh , TRUE() ))

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Try to modify your formula like below:

    WithinThreshold =
VAR Threshold =
    ROUND ( CALCULATE ( SUM ( 'Stock'[DC QOH] ) ) * 1.2, 0 )
RETURN
    IF (
        CALCULATE ( SUM ( 'Stock'[DC QOH] ), ALL ( 'Stock' ) ) < Threshold,
        TRUE (),
        FALSE ()
    )

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This isn't working for my model, im still getting the same repeating values on my measure. Is this an issues with the way my model is set up?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Maybe yes, you can provide some screenshot information and test data about the model for me to troubleshoot as soon as possible.

     

    Best Regards,
    Adamk Kong

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello, Thank you for your help. I was able to solve my problem using another method. The issue due to 2 dim table columns being used in the matrix. 

      I used this video: https://www.youtube.com/watch?v=3vcKgN8HxjY

       

      and 

      IF(COUNTROWS('(Live) Store Stock') = 1,if(CALCULATE(SUM('Stock'[DC QOH]) ) < thresh , TRUE() ))