Forum Discussion

Fxelixe's avatar
Fxelixe
Frequent Visitor
3 years ago
Solved

IF statement not adding on Category Level

Hello,

 

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

Here's my data set:

 

GroupIDXYZHave X Y Z
AA011000010000100001
AA021000010000100001
AA0301000000
AA0401000000
AA0501000000
AA061000010000100001
AA07100001000000
AA081000010000100001
BA0910000000
BA10100000100000
BA1100100000
BA1200100000
BA1300100000
BA14010000100000
BA15010000100000
BA161000010000100001
BA17100001000000
BA1810000000
CA1910000000
CA2010000000
CA2110000000
CA2210000000
CA2301000000
CA24010000100000
CA251000010000100001
CA261000010000100001
CA271000010000100001
CA281000010000100001

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

Desisred Output
A4
B1
C4


But on DAX, I Got 

Row LabelsSum of Have X Y Z
A1
B1
C1
Grand Total1


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's avatar
    Jihwan_Kim
    3 years ago

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

     

4 Replies

    • Fxelixe's avatar
      Fxelixe
      Frequent Visitor
      3 years ago

      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)

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      Fxelixe
      Frequent Visitor
      3 years ago

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