Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out data between to sizes

I have a very large CSV data set with length, width, and height, columns, I am trying to filter out the data that will only fit between my criteria below.  I want to narrow my master list down to the...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - I fixed the duplicates issue I believe and corrected a syntax error. PBIX is attached for reference, Table 16 (below sig)

    Column = 
  VAR __Table = { [Length], [Width], [Height] }
  VAR __Length = MAXX(__Table,[Value])
  VAR __Height = MINX(__Table,[Value])
  VAR __Width = SWITCH(TRUE(),
    COUNTROWS(FILTER(__Table,[Value] = __Length))>1,__Length,
    COUNTROWS(FILTER(__Table,[Value] = __Height))>1,__Height,
    MAXX(FILTER(__Table,[Value] <> __Length && [Value] <> __Height),[Value])
    )
RETURN
  SWITCH(TRUE(),
    __Length <= 8.26 && __Height <= .4 && __Width <= 7.87,"Small",
    __Length <= 21 && __Height <= 11.4 && __Width <= 13.4,"Large",
   "Does not fit"
  )

      

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Anonymous - Yes, just the first line is where you would swap out [Length], [Height] and [Width].

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I get errors when I use this:

Column =
VAR __Table = { [UOM_Length], [UOM_Width], [UOM_Height] }
SWITCH(TRUE(),
MAXX(__Table,[Value]) <= 8.26 && MINX(__Table,[Value]) <= .4 && MAXX(FILTER(__Table,[Value] <> MAXX(__Table,[Value]) && [Value] <> MINX(__Table,[Value]),[Value]) <= 7.87,"Small",
MAXX(__Table,[Value]) <= 21 && MINX(__Table,[Value]) <= 11.4 && MAXX(FILTER(__Table,[Value] <> MAXX(__Table,[Value]) && [Value] <> MINX(__Table,[Value]),[Value]) <= 13.4,"Large",
"Does not fit"
)
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - Yes, because I had a syntax error, try this:

     

    Column = 
  VAR __Table = { [UOM_Length], [UOM_Width], [UOM_Height] }
  VAR __Length = MAXX(__Table,[Value])
  VAR __Height = MINX(__Table,[Value])
  VAR __Width = SWITCH(TRUE(),
    COUNTROWS(FILTER(__Table,[Value] = __Length))>1,__Length,
    COUNTROWS(FILTER(__Table,[Value] = __Height))>1,__Height,
    MAXX(FILTER(__Table,[Value] <> __Length && [Value] <> __Height),[Value])
    )
RETURN
  SWITCH(TRUE(),
    __Length <= 8.26 && __Height <= .4 && __Width <= 7.87,"Small",
    __Length <= 21 && __Height <= 11.4 && __Width <= 13.4,"Large",
   "Does not fit"
  )