topic Re: IF statement not adding on Category Level in DAX Commands and Tips

IF statement not adding on Category Level

Fxelixe — Fri, 16 Dec 2022 04:06:50 GMT

Hello,

So I got some issue on applying my measure on higher levelv of category.

Here's my data set:

Group	ID	X	Y	Z	Have X Y Z
A	A01	10000	10000	10000	1
A	A02	10000	10000	10000	1
A	A03	0	10000	0	0
A	A04	0	10000	0	0
A	A05	0	10000	0	0
A	A06	10000	10000	10000	1
A	A07	10000	10000	0	0
A	A08	10000	10000	10000	1
B	A09	10000	0	0	0
B	A10	10000	0	10000	0
B	A11	0	0	10000	0
B	A12	0	0	10000	0
B	A13	0	0	10000	0
B	A14	0	10000	10000	0
B	A15	0	10000	10000	0
B	A16	10000	10000	10000	1
B	A17	10000	10000	0	0
B	A18	10000	0	0	0
C	A19	10000	0	0	0
C	A20	10000	0	0	0
C	A21	10000	0	0	0
C	A22	10000	0	0	0
C	A23	0	10000	0	0
C	A24	0	10000	10000	0
C	A25	10000	10000	10000	1
C	A26	10000	10000	10000	1
C	A27	10000	10000	10000	1
C	A28	10000	10000	10000	1

On Excel we can use if and countif statement to determine the have XYZ parameter

Desisred Output
A	4
B	1
C	4

But on DAX, I Got

Row Labels	Sum of Have X Y Z
A	1
B	1
C	1
Grand Total	1

Here's my DAX Calculation

Have XYZ =

Var __Unit = 'Value'[Sales]

Var Item1 =

CALCULATE(IF(isblank('Value'[Sales]),0,IF('Value'[Sales]>0,1,0)),filter('Time','Time'[Month] = "Nov"),filter('Product','Product'[Code] = "X"))

Var Item2 =

CALCULATE(IF(isblank('Value'[Sales]),0,IF('Value'[Sales]>0,1,0)),filter('Time','Time'[Month] = "Nov"),filter('Product','Product'[Code] = "Y"))

Var Item3 =

CALCULATE(IF(isblank('Value'[Sales]),0,IF('Value'[Sales]>0,1,0)),filter('Time','Time'[Month] = "Nov"),filter('Product','Product'[Code] = "Z"))

Return

IF((Item1+Item2+Item3)=3,1,0)

Thank you

Re: IF statement not adding on Category Level

tamerj1 — Fri, 16 Dec 2022 04:20:51 GMT

Hi Fxelixe
[Group] and [ID] from which table(s)? [Sales] is a measure right?

Re: IF statement not adding on Category Level

Fxelixe — Fri, 16 Dec 2022 04:29:06 GMT

the group itself is on seperate table, [Sales] is column. the DAX only for "Have XYZ" Calculation. after that I drag group to the row and the Have XYZ measure to the value section.

its challenging for me to create logic of ID that buy specific products (in this case X Y Z). and then applies it of group level because the IF statement on my measure also applied on region level (i expect only applied on id level and sum it on region level)

Re: IF statement not adding on Category Level

Jihwan_Kim — Fri, 16 Dec 2022 04:30:04 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Desired outcome measure: = SUMX ( ADDCOLUMNS ( DISTINCT ( 'Group'[ID] ), "@condition", IF ( COUNTROWS ( CALCULATETABLE ( FILTER ( 'Value', 'Value'[Sales] <> 0 ), 'Time'[Month] = "Nov" ) ) = 3, 1 ) ), [@condition] )

Re: IF statement not adding on Category Level

Fxelixe — Sun, 18 Dec 2022 09:54:38 GMT

Thank you for the inspiration. hope this also can help others.