Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
vnue
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   
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

 

2 REPLIES 2
vnue
New Member

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

 

 

 

Anonymous
Not applicable

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

vyiruanmsft_0-1694759290583.png

2. Create a dimension table

vyiruanmsft_1-1694759441644.png

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)

vyiruanmsft_2-1694759541849.png

Best Regards

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.