cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

DAX with Condition Using IF or OR

HI ,
i'm working on Condition dax which a bit complicate over here and i have never experienced this before. 

We have code , must have SKU etc.. and Count. in this case , BI will count all number and sum together over the different code provide. but i wanna have exceptional over few codes.  

 

 

Look at yellow highlight on 1317 count 1 , 1108 count 1 , both of them will be 2 as calculation. 

 

but this is exeption :  IF 1317 count 1 and 1108 count 1 both of them will count  only 1 , 

IF 4295 count 1 and 2239 count 1 both of them will count only 1 

 

For the rest let's calculate as normal sum. 

 

 

 1.PNG

6 REPLIES 6
Super User
Super User

Re: DAX with Condition Using IF or OR

Hi @Chanleakna123 

 

I'll assume you want this as a measure (in a card visual for instance):

 

Measure =
VAR _Sum1 =
    CALCULATE (
        SUM ( Table1[Count] );
        NOT Table1[Code] IN { 1317; 1108; 4295; 2239 }
    )
VAR _Count1317 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 1317 )
VAR _Count1108 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 1108 )
VAR _Count4295 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 4295 )
VAR _Count2239 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 2239 )
VAR _Sum2 =
    IF (
        _Count1317 = 1
            && _Count1108 = 1;
        1;
        CALCULATE ( SUM ( Table1[Count] ); Table1[Code] IN { 1317; 1108 } )
    )
VAR _Sum3 =
    IF (
        _Count4295 = 1
            && _Count2239 = 1;
        1;
        CALCULATE ( SUM ( Table1[Count] ); Table1[Code] IN { 4295; 2239 } )
    )
RETURN
    _Sum1 + _Sum2 + _Sum3

 

 

Re: DAX with Condition Using IF or OR

HI @AlB  i am not sure what happen here Smiley Sad can you please help me 

 

1.PNG

Re: DAX with Condition Using IF or OR

hi @AlB  can you please help me with this one ? 
It's error Smiley Sad 

Super User
Super User

Re: DAX with Condition Using IF or OR

@Chanleakna123 

 

You probably have to swap the semicolons ";" for commas "," in the code, if you have a US-like locale in your OS.

 

Re: DAX with Condition Using IF or OR

 

 

hi @AlB  thx for your effort .

i used above calculation as you mentioned . and when i do testing filter on code number that i put together with Customer Code , it turn out to wrong number , actually the right data is  4295= 1 , 2239= 1 = Total 1 

it should not be the number as 8 , 9 or 10 , it's not as expected. because my original data only show count 1 on each customer code with Code ,  can you please help to correct this? i'm looking forward. 

 

1.PNG

 

2.PNG3.PNG

Highlighted

Re: DAX with Condition Using IF or OR

@AlB  , can you please help me to solve this ? really need your quickk support