filtering based on both columns and rows

[vnue]

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.

Ingredient	X index	X min	X max	Y index	Y min	Y max	Z index	Z min	Z max
A	3	71.8	72.8	3	24	26	3		4
B	3	27.2	28.2				3		0.02
C	2		1	2		1	2		1
D	2		10	2		10	2		10
E	2		4	2		4	2		4
F	2		2	2		2	2		2
G				2		10
H	1	27.2	28.2	1	74	76	1	96

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:

Ingredient	A	B	C	D	E	F	G	H
X index	3	3	2	2	2	2		1
X min	71.8	27.2						27.2
X max	72.8	28.2	1	10	4	2		28.2
Y index	3		2	2	2	2	2	1
Y min	24							74
Y max	26		1	10	4	2	10	76
Z index	3	3	2	2	2	2		1
Z min								96
Z max	4	0.02	1	10	4	2

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:

ingredient	A	B	C	D	E	F	G	H
X index	3	3	2	2	2	2		1
X min	71.8	27.2						27.2
X max	72.8	28.2	1	10	4	2		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

[vnue]

Hi @v-yiruan-msft 

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:

Ingredient	A	B	C	D	E	F	H
X index	3	3	2	2	2	2	1
X min	71.8	27.2					27.2
X max	72.8	28.2	1	10	4	2	28.2

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

Ingredient	C	D	E	F
X index	2	2	2	2
X min
X max	1	10	4	2

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

Ingredient	A	B	C	D	E	F	H
X min	71.8	27.2					27.2
X max	72.8	28.2	1	10	4	2	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

[v-yiruan-msft]

Hi @vnue ,

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