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  - Maybe something like:

 

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

There's a problem to be solved for duplicate values.

 

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