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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LucieK
Frequent Visitor

Iterating over columns , or "filtering rows" using DAX

I'm new to Power Bi and I'm trying to reproduce some operations which I know how to do in Python. I have a table which is a square asymmetric matrix of the type
 
      A     B     C       D
A    1     0.3   0.8    0.7
B    0.5   1     0.2    0.1
C    0.2   0.4   1      0.9
D   0.7    0.6   0.2   1
 
The column headers are identical to row names.
 
I've created a Parameter "slider" to select values between 0 and 1 and I also have a slicer populated with the existing column headers (ie A,B,C,D).
 
The user will select the Parameter value using the slider, and the 2nd input value will be the selected header, let's call it a Thing.
Based on these selections, I need to create a filtered table so that for the selected Thing row, only columns with values >= Parameter are kept and also only the matching rows.
 
So for eg for Parameter = 0.7 and Product = A the desired output will be generated by considering the row A and comparing all values in the A row with the Parameter - this means column B must be removed, and thus also the row B must be removed, resulting in the following desired output:
 
    A      C      D
A  1      0.8   0.7
C  0.2   1      0.9
D  0.7   0.2   1
 
Is this possible using DAX without transposing the table first (I need to do more operations, filtering in both directions)?
2 REPLIES 2
LucieK
Frequent Visitor

Thanks Ibendlin for your reply. I can try to reduce my question to the issue of filtering tables "horizontally" instead of "vertically" : is it possible using DAX without transposing, and without unpivoting? 

 

The sample data above are typed (not a screenshot).

 

I know how to filter "vertically", ie for a particular column, keep only rows where the value in the particular column matches a condition. Say I want to filter the sample matrix I have given above so that I only keep rows where the value in column A is >=0.5: 
mynewtable = FILTER('thematrixabove', 'thematrixabove'[A] >=0.5)
will yield
      A     B     C       D
A    1     0.3   0.8    0.7
B    0.5   1     0.2    0.1
D   0.7    0.6   0.2   1
 
Is this possible "horizontally", ie keep only columns where the value in a particular ROW  matches a condition, without transposing the table first?
Something like 
mynewtable = FILTER('thematrixabove', 'thematrixabove'.rowlabel[A] >=0.5)
to yield
 
      A        C        D
A    1        0.8      0.7
B    0.5      0.2     0.1
C    0.2      1        0.9
D   0.7       0.2     1
 

 I suspect it's not possible; I've searched and haven't found anything so I guess the only option is to keep transposing back and forth? I later realized I could also unpivot it but that's also a messy workaround, I'd like to know if there is a "native" way of iterating over columns.

lbendlin
Super User
Super User

 I'm trying to reproduce some operations which I know how to do in Python.

That's called "fighting the API".  Don't do that. Embrace Power BI for what it is, and use other tools if Power BI is not suitable.

 

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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