Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

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   

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:

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


That leaves me with:

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



New Member

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:

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:

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: 

X min71.827.2     27.2
X max72.828.211042



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:



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




Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors