Getting an IF statement to work

[Anonymous]

Hi everyone,

I am trying to show when a product is sold above or below the Price per Unit.  For example if a product is sold for $10 and the minimum is $8, the IF statement should return Above.  And if a product is being sold for $6 and the minimum is $8 the IF statement should return Below.  I tried using this dax formula but when I put it into the table it removes values from the Price per Unit column and the Lowest Price column.  It also seems to add products that have already been filtered out back into the table when adding the Y/N measure.  Not sure if it would create an issue but the Lowest price measure pulls from a different dataset but are connected through relationship.

These are the DAX formulas I've created.

Price per Unit = DIVIDE('Measures (2)'[Sum20-21],'Measures (2)'[SumSingleUnits])

Lowest Price = SUM('Unit Price'[Price])

Y/N = IF([Price per Unit] < [Lowest Price], "Below","Above")

The rows that have $15 should be Above and the rows that are $0.01 should be Below

With the added Y/N measure

Thank you for the help!

[parry2k]

@Anonymous that shouldn't matter if there is a common dimension between these two tables. Not sure how the data model is setup

[Anonymous]

@parry2k  hmmm weird.  I thought I had created the relationship the right way I'll have to do some more digging around in the data.  Thanks for the help!

[parry2k · Accepted solution]

@Anonymous sorry missed the comma (,) after TRUE

Y/N =
VAR __priceperUnit = [Price Per Unit]
VAR __lowestprice = [Lowest Price]
RETURN
SWITCH ( TRUE(),
  __priceperUnit == BLANK() || __lowerprice == BLANK(), BLANK(),
  __priceperUnit < __lowestprice, "Below",
  "Above"
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi again @parry2k ,

I think I may have spoke too soon 😅.  It looks like it is just the Below part that isn't quite matching up.  Would the issue casuing this be because data is coming from two different data sources? Thank you for the help!

[Anonymous]

Hi @parry2k ,

That worked perfectly!  Thank you so much for the help!

[parry2k]

@Anonymous try this, you need to check blank()

Y/N =
VAR __priceperUnit = [Price Per Unit]
VAR __lowestprice = [Lowest Price]
RETURN
SWITCH ( TRUE()
  __priceperUnit == BLANK() || __lowerprice == BLANK(), BLANK(),
  __priceperUnit < __lowestprice, "Below",
  "Above"
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k ,

I am trying to use your formula but it is throwing a syntax error on the __priceperUnit in row 6.  I'm not quite sure what I'm missing or why its not working.  I tried renaming it but no luck there.  Thank you for the help!