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

      

Anonymous's avatar
Anonymous
Not applicable
6 years ago

My table column are labeled UOM_LENGHT, UOM_Height, and UOM_WIDTH, do I just swap that out in our text below for all the places it shows?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
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"
  )