Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

filtering based on both columns and rows

Hi Everyone

 

So this is a bit of a long story, but please bear with me. I have a table consisting of ingredient index, min and max values for several different analysis. See sample below - the data is a lot bigger.

IngredientX indexX minX maxY indexY minY maxZ indexZ minZ max
A371.872.8324263 4
B327.228.2   3 0.02
C2 12 12 1
D2 102 102 10
E2 42 42 4
F2 22 22 2
G   2 10   
H127.228.217476196 

I need to find the places where the columnname contains a specific name and the index has a specific value. The name of the column i.e. the X, Y, Z should be able to change dynamically. The value of the index that it searches for should be able to change dynamiccaly as well. Afterwards I need to be able to take a row and compare the values for min and max to a different table - so I need the first column to end up being a column still.

My initial solution was to transpose the data before loading it:

IngredientABCDEFGH
X index332222 1
X min71.827.2     27.2
X max72.828.211042 28.2
Y index3 222221
Y min24      74
Y max26 110421076
Z index332222 1
Z min       96
Z max40.0211042  


Create a measure (f.ex.):
ingredient = X

 

And then filter

filter1 =
Var ing = [ingredient]
 
Var tab1 =
FILTER(transposedvalues, CONTAINSSTRING(transposedvalues[ingredient], ing))

RETURN
tab1


That leaves me with:

ingredientABCDEFGH
X index332222 1
X min71.827.2     27.2
X max72.828.211042 28.2


Now i want to filter so that only where X index is 1 or 2 is part of the table. My best solution is to transpose the table again, but I don't know how to that properly in DAX and I can't seem to find a solution online that functions for this case. I would very much like to keep it all as virtual tables and therefore I can't use Power Query.


Does anyone have a solution to this? Any suggestion is helpful, I really just need a hint in the right direction.

Thank you in advance

Vnue

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Unpivot the columns [xx index],[xx min] and [xx max] in Power Query Editor

    = Table.Unpivot(#"Changed Type", {"X index", "X min", "X max", "Y index", "Y min", "Y max", "Z index", "Z min", "Z max"}, "Attribute", "Value")

    2. Create a dimension table

    3. Create a measure as below

    Flag = 
VAR _selindex =
    SELECTEDVALUE ( 'Indexes'[Index Value] )
VAR _selattr =
    SELECTEDVALUE ( 'Table'[Attribute] )
RETURN
    IF ( IFERROR ( FIND ( _selindex, _selattr, 1, 0 ), 0 ) > 0, 1, 0 )

    4. Create a matrix visual with the visual-level filter (Flag is 1)

    Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    So this partially works. The unpivoting works as intended and end up with this based on your suggestion - if i show it in a visual:

    IngredientABCDEFH
    X index3322221
    X min71.827.2     27.2
    X max72.828.211042 28.2

     

    I need this to be filtered based on the index. i.e. if the index is 2 then this table should be:

    IngredientCDEF
    X index2222
    X min    
    X max11042

     

    I can separate the index into another table if that helps. 
    In that case I can get to this: 

    IngredientABCDEFH
    X min71.827.2     27.2
    X max72.828.211042

     28.2

     

    Which then needs to be filtered by the index table which reduces to this by using the same actions you suggested before and filtering on the index number 2 in this example:

    C
    D
    E
    F

     

    Does that make sense at all?

     

    As this is just the first part of a much longer process I need to keep it all virtual if at all possible. The point of this part is to reduce the table as much as possible before I start comparing with some other data, so I don't end up overloading the memory. 

     

    Thank you for your help
    Best Regards
    Vnue