Function SELECTEDVALUE

[Anonymous]

Hello!

I need help please. I have different values in a filter, and I need to apply a measure to them. However, for some of them, this measure needs to be modified. In excel it would be easy with an IF fucntion, however I am not able to find the solution for power bi:

1.  Company 1

2. Company 2

3. Company 3

 Measure := Sales[Quantity] * Sales[Net Price]

For Company 2, however, I need: 0,5*(Sales[Quantity] * Sales[Net Price])

So I am able to build this fuction: 

IF(SELECTEDVALUE('Companies'[Company])="Company 2";0,5*(Sales[Quantity] * Sales[Net Price]));Sales[Quantity] * Sales[Net Price])

The problem is that when I build a table with the 3 companies, as the three are "Selected Values", it does not apply the 0,5*(Sales[Quantity] * Sales[Net Price]) for Company 2.

What should I use instead of "SELECTEDVALUE"?

Thanks so much for your help!!!

[PaulDBrown · Accepted solution]

@Anonymous 

Not sure what is going on at your end, but the SELECTEDVALUE function does identify the filter context in a table:

Are the columns in both the IF statement and the visual from the same table?

[Anonymous]

 @Anonymous thanks for the answer but the problem is that I need to use a measure becasue the column does not work for the totals. It sums everything.

@PaulDBrown  thanks! you were right, I was using another file for "Company", so it did not work. Now it works, but, last challenge: for totals it does not apply the different measures, do you know the logic behind the totals? Thank you so much!!!!

[PaulDBrown]

@Anonymous

You probably need a measure along the lines of:


Company Totals = SUMX(SUMMARIZE('Companies'; 'Companies'[Company]; "result"; [IF measure]); result)

See if it works

Ps: the reason the totals is returning the default calculation, thus ignoring the IF clause, is because the totals does not discriminate individual table values. In other words, your IF clause at total level checks if the IF statement is true (which it isn’t since the affected company is not in the filter context of totals) and applies the stipulated logic. It’s easier to understand if you think about creating a simple card visual with the same IF statement. Since there is no company filter context , the IF statement does not apply and therefore computes the default calculation (the calc returned when the IF clause returns FALSE). DAX has no way of knowing the desired result without the necessary filter context
The solution I’ve suggested circumvents this, since it creates a virtual table which summarizes the company values with each corresponding result from the IF clause, and then SUMX kicks in to sum up on a row by row basis of this virtual table.
Not sure if my explanation is clear...but let me know if you still have doubts.

[Anonymous]

Hi!

What about if you create a new calculate column and you apply your measure to it? 

Something like this: 

MEASURE = IF(FILTER('YOUR TABLE';'COMPANY' = "COMPANY2");0,5*(Sales[Quantity] * Sales[Net Price]));Sales[Quantity] * Sales[Net Price])