Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Saxon202202
Helper III
Helper III

If statement based on the count

Hi,

ItemModeUK PalletUS PalletEURO PalletStatus
123Train350450600Same Pallet Size For Both Mode
123Air350450600Same Pallet Size For Both Mode
124Train150150150Unique Pallet Size For Both Mode
124Air350450600Unique Pallet Size For Both Mode
125Air898989Different Pallet Size Within The Mode
125Air457846Different Pallet Size Within The Mode
125Air231548650Different Pallet Size Within The Mode
125Air890790250Different Pallet Size Within The Mode
126Train650450360Different Pallet Size Within The Mode
126Train250380650Different Pallet Size Within The Mode
126Train150250350Different Pallet Size Within The Mode
126Train100300600Different Pallet Size Within The Mode
127Air236458547Different Pallet Size For Both Mode
127Air233158460Different Pallet Size For Both Mode
127Air231548650Different Pallet Size For Both Mode
127Air890790250Different Pallet Size For Both Mode
127Train150250350Different Pallet Size For Both Mode
127Train100300600Different Pallet Size For Both Mode
127Train225355555Different Pallet Size For Both Mode
128Air654258478Single Pallet Size For Sigle Mode
129Train765457657Single Pallet Size For Sigle Mode
130Train121212Single Pallet Size For Sigle Mode
131Air181779Single Pallet Size For Sigle Mode

 

Condition
1. If the same item has different mode and where the count equal to 1 based on the item & mode and UK, US & EURO sizes are same then return -“Same Pallet Size For Both Mode”. 
2 .If the same item has different mode and where the count equal to 1 based on the item & mode and UK, US & EURO sizes are different then return - “Unique Pallet Size For Both Mode”.
3.If the same item has same mode and where the count greater than 1 based on the item & mode and UK, US & EURO sizes are different then return - “Different Pallet Size Within The Mode”.
4.If the same item has different mode and where the count greater than 1 based on the item & mode and UK, US & EURO sizes are different then return - “Different Pallet Size Within The Mode”.
5.If item and mode have unique count, then return “Single Pallet Size For Sigle Mode”.

I am looking for DAX code to get the result.

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@Saxon202202 

output : 

Daniel29195_0-1707421715578.png

 

DAX : 

calculated column = 
var itm  =  Table_4[Item]
var uk_pallet = Table_4[UK Pallet]
var us_pallet = Table_4[US Pallet]
var euro_pallet = Table_4[EURO Pallet]
var ds =  
ADDCOLUMNS(
    filter(
        ALLNOBLANKROW(Table_4),
        Table_4[Item] = itm
    ),
    "@X", CALCULATE(DISTINCTCOUNT(Table_4[Mode]),ALLEXCEPT(Table_4,Table_4[Item])), -- check if we have more than 1 mode per item
    "@C" , CALCULATE(COUNT(Table_4[Mode]), ALLEXCEPT(Table_4,Table_4[Item],Table_4[Mode])) -- check nb  item-mode existence 
)

var suk =  SUMX(ds,Table_4[UK Pallet])
var sus =  SUMX(ds,Table_4[US Pallet])
var seuro =  SUMX(ds,Table_4[EURO Pallet])

var suk_same = suk/countrows(ds) = uk_pallet
var sus_same = sus/countrows(ds) = us_pallet
var seuro_same = seuro/countrows(ds) = euro_pallet

var C=  
MAXX(
    ds,
    [@C]
)
--check if has different mode 
-- if check returns 0 , then we hav only 1 mode per this item.
var X = 
MAXX(
    ds
,[@X])



var res = 
SWITCH(
    TRUE(),
    X > 1 && C = 1 &&  suk_same = TRUE() && sus_same = TRUE() && seuro_same = TRUE() , "Same Pallet Size For Both Mode",
    X > 1 && C = 1 &&   (suk_same = False() || sus_same = False() || seuro_same = False()) , "Unique Pallet Size For Both Mode",
    X = 1 && C > 1 &&   (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size Within The Mode",
    X > 1 && C > 1 &&   (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size For Both Mode",
    x=1 && c = 1 ,"Single Pallet Size For Sigle Mode"

   

)

return res -- CONCATENATEX(ds,[@X] & "-" & [@C] ,",")

 

 

 

replace the table name with your table name and the coluymn names with your column names .

 

 

let me know if it works for you .

 

 

 

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

 

View solution in original post

4 REPLIES 4
Daniel29195
Super User
Super User

@Saxon202202 

output : 

Daniel29195_0-1707421715578.png

 

DAX : 

calculated column = 
var itm  =  Table_4[Item]
var uk_pallet = Table_4[UK Pallet]
var us_pallet = Table_4[US Pallet]
var euro_pallet = Table_4[EURO Pallet]
var ds =  
ADDCOLUMNS(
    filter(
        ALLNOBLANKROW(Table_4),
        Table_4[Item] = itm
    ),
    "@X", CALCULATE(DISTINCTCOUNT(Table_4[Mode]),ALLEXCEPT(Table_4,Table_4[Item])), -- check if we have more than 1 mode per item
    "@C" , CALCULATE(COUNT(Table_4[Mode]), ALLEXCEPT(Table_4,Table_4[Item],Table_4[Mode])) -- check nb  item-mode existence 
)

var suk =  SUMX(ds,Table_4[UK Pallet])
var sus =  SUMX(ds,Table_4[US Pallet])
var seuro =  SUMX(ds,Table_4[EURO Pallet])

var suk_same = suk/countrows(ds) = uk_pallet
var sus_same = sus/countrows(ds) = us_pallet
var seuro_same = seuro/countrows(ds) = euro_pallet

var C=  
MAXX(
    ds,
    [@C]
)
--check if has different mode 
-- if check returns 0 , then we hav only 1 mode per this item.
var X = 
MAXX(
    ds
,[@X])



var res = 
SWITCH(
    TRUE(),
    X > 1 && C = 1 &&  suk_same = TRUE() && sus_same = TRUE() && seuro_same = TRUE() , "Same Pallet Size For Both Mode",
    X > 1 && C = 1 &&   (suk_same = False() || sus_same = False() || seuro_same = False()) , "Unique Pallet Size For Both Mode",
    X = 1 && C > 1 &&   (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size Within The Mode",
    X > 1 && C > 1 &&   (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size For Both Mode",
    x=1 && c = 1 ,"Single Pallet Size For Sigle Mode"

   

)

return res -- CONCATENATEX(ds,[@X] & "-" & [@C] ,",")

 

 

 

replace the table name with your table name and the coluymn names with your column names .

 

 

let me know if it works for you .

 

 

 

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

 

@Daniel29195 , Thanks for your support and help. It's working fine.

Daniel29195
Super User
Super User

@Saxon202202 

 

where the count = 1  ?  count of what  ? 

 

 

@Daniel29195 count for item and mode

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors