IF statement not adding on Category Level

[Fxelixe]

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

[Jihwan_Kim · Accepted solution]

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]
)

[Fxelixe]

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

[tamerj1]

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

[Fxelixe]

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)