Forum Discussion
IF - column as logical test
Hello Everyone,
I need to use a column as logical test in a IF formula (in a measure) but...
It doesn't work.
EX.
IF( CLIENT[Category]="C";
sum(...);;)
No suggestion appeare if I try to write the name (only suggestion of functions and measures) and If I try to force it,
the error message is the following: "
It is no possible to obtain an single value from the colum CLIENT[Category]"
What am I doing wrong?
thank you very much
Paolo
3 Replies
Hello Paolo,
How are you?
What exactly you need? Because if you need to SUM with filters, i highly reccomend to use a "New Measure" instead of "New Column".
Try using a new measure with CALCULATE.
Example: CALCULATE(Sum(sales);CLIENT[Category]="C"))
In this example the measure is summing all the sales of the client category C.
I hope that works for you!
Alex.
Thanks for your reply,
of course I can use CALCULATE function but in this way I can write only the calculation I need if the Category is "C":
CALCULATE(Sum(sales);CLIENT[Category]="C"))
I need something that change the calculation if the category is not "C".
This result is easy to reach by a new column:
(IF (CLIENT[Category]="C"; Sum(sales); "")
Is it possible using a measure?
I try do better explain (these are only a case study):
in the CATEGORY column I can have 5 different values: (C-F-S-O-D)
CATEGORY
PRODUCT ID
DISCOUNT
PRICE
C
002
10
20
O
008
5
21
S
005
5
22
F
009
15
12
C
004
12
10
C
003
8
5
we need for a measure to make the following calculation:
--------------------------
IF CATEGORY is "C" do the following calculation: SUM(PRICE) * SUM(DISCOUNT)
Otherwise BLANK.
--------------------------
A measure (XYZ) that if I create a new table as object in Power BI, I can obtain something as follow:
Product ID
XYZ
003
21
007
28
009
004
003 and 007 are Category "C" (logical test = true)
009 and 004 are Category "O" (logical test = false)
Is it possible or I only can use a new Column?
thank you very much
Paolo
- Greg_Deckler
Community Champion
You need to use aggregation for columns in a measure, like:
IF( MAX(CLIENT[Category])="C";
