cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Conditional Measure Not Working the way I Imagined - any help greatly appreciated

So I have built a Sales Effciency data model with visuals showing effciency ratios each month of the year.

I had a request to show which stores have shown effciency improvements month-over-month, and which stores have declined in effciency month-over-month. I would like to return a binary conditional where 0 indicates a gain and 1 indicates a decline, to be used for Green and Red color coding respectively on a map visual.

Before I get the Map visual going, I wanted to test my measure in a table view, here is a quick screenshot:

The first three measures in the example table work correctly - I'll paste how to measures are defined below just for reference - but as you can see my Eff MoM Conditional is returnign zeros for all stores but I would like for any stores with a negative MoM Efficiency % Chg to return a 1.

My conditional measure is just a simple If Statement, so maybe there is another way I could build that measure to return the results I was expecting. I have played around with this a good deal and would appreciate any insights you may have. Thanks for your time.

1 ACCEPTED SOLUTION
Super User

probably it is returning value as a whole number, not in decimal. try dividing with 100. see if that works.

Eff MoM Conditional =
IF (
[Efficiency % Chg] / 100 < 0,
1,

)

Proud to be a Super User!

5 REPLIES 5
Frequent Visitor

Hello Rubayatyasmin, thanks so much for your reply but I'm afraid that edit is returning the same results (screenshot below). I did consider the percentage --> decimal relationship as DAX reads it, but I figured anything less than 0 as you suggested would work too:

Super User

probably it is returning value as a whole number, not in decimal. try dividing with 100. see if that works.

Eff MoM Conditional =
IF (
[Efficiency % Chg] / 100 < 0,
1,

)

Proud to be a Super User!

Frequent Visitor

Thx again for your help, that's a good idea but I'm afraid it didn't change the output either

I also tried using parenthesis to isolate the /100 in terms of order of operations hoping that might work, but no changes:

I will keep researching a solution to this, I think I may have to tell DAX to calculate based on individual Store #'s somehow. Thanks for all of your help

Super User

Are you certain that MOM calculation is giving the right value?

check the table relationship.

Proud to be a Super User!

Super User

Your value is in %. That is causing the issue. When dealing with percentage values, keep in mind that they are represented as decimals in DAX calculations. For example, if your "Efficiency % Chg" measure returns a value of 5%, it will be represented as 0.05 in DAX.

try using. <0, 1,0

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors