Iterating over columns , or "filtering rows" using DAX

Question

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&nbsp;&nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp;DA&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp;0.3&nbsp; &nbsp;0.8&nbsp; &nbsp; 0.7B&nbsp; &nbsp; 0.5&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; 0.1C&nbsp; &nbsp; 0.2&nbsp; &nbsp;0.4&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; 0.9D&nbsp; &nbsp;0.7&nbsp; &nbsp; 0.6&nbsp; &nbsp;0.2&nbsp; &nbsp;1&nbsp;The column headers are identical to row names.&nbsp;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).&nbsp;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 &gt;= Parameter are kept and also only the matching rows.&nbsp;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:&nbsp;&nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; DA&nbsp; 1&nbsp; &nbsp; &nbsp; 0.8&nbsp; &nbsp;0.7C&nbsp; 0.2&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; 0.9D&nbsp; 0.7&nbsp; &nbsp;0.2&nbsp; &nbsp;1&nbsp;Is this possible using DAX without transposing the table first (I need to do more operations, filtering in both directions)?

lbendlin · Answer

I'm trying to reproduce some operations which I know how to do in Python.
That's called "fighting the API".&nbsp; Don't do that. Embrace Power BI for what it is, and use other tools if Power BI is not suitable.
&nbsp;
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-Forum/ba-p/963216 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/1447523

luciek · Answer

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?&nbsp;&nbsp;The sample data above are typed (not a screenshot).&nbsp;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 &gt;=0.5:&nbsp;mynewtable = FILTER('thematrixabove', 'thematrixabove'[A] &gt;=0.5)will yield&nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp;DA&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp;0.3&nbsp; &nbsp;0.8&nbsp; &nbsp; 0.7B&nbsp; &nbsp; 0.5&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; 0.1D&nbsp; &nbsp;0.7&nbsp; &nbsp; 0.6&nbsp; &nbsp;0.2&nbsp; &nbsp;1&nbsp;Is this possible "horizontally", ie keep only columns where the value in a particular ROW&nbsp; matches a condition, without transposing the table first?Something like&nbsp;mynewtable = FILTER('thematrixabove', 'thematrixabove'.rowlabel[A] &gt;=0.5)to yield&nbsp;&nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; DA&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; 0.8&nbsp; &nbsp; &nbsp; 0.7B&nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp;0.1C&nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; 0.9D&nbsp; &nbsp;0.7&nbsp; &nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; &nbsp;1&nbsp;&nbsp;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.

Forum Discussion

Iterating over columns , or "filtering rows" using DAX

2 Replies

Related content

Building slicers that do not filter out "global" rows

M query Filtered = #"Filtered Rows"{0}[Content]

Escape row delimiter within quotes

Replacing values with Iterations

Cannot filter on "blank" rows.