topic Re: IF - column as logical test in DAX Commands and Tips

IF - column as logical test

paolom — Tue, 17 Mar 2020 14:38:29 GMT

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

Re: IF - column as logical test

alextf — Tue, 17 Mar 2020 14:49:29 GMT

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.

Re: IF - column as logical test

Greg_Deckler — Tue, 17 Mar 2020 14:53:52 GMT

You need to use aggregation for columns in a measure, like:

IF( MAX(CLIENT[Category])="C";

Re: IF - column as logical test

paolom — Wed, 18 Mar 2020 16:06:13 GMT

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