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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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 @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:

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

 

 

 

v-yiruan-msft
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")

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

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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.