Forum Discussion
Measure to filter only required column values
Hi Gaz_Chapp
Try this measure to create a new table by DAX code:
Filtered Table = filter('Table','Table'[TransAtmEftposId] in {"9V12345D","9V54321D"})Original Table :
Filtered Table:
In Power Query add click on "Insert Step After" and use this code [add new codes with OR at the end]:
= Table.SelectRows(#"Changed Type", each ([TransAtmEftposId] = "9V12345D" or [TransAtmEftposId] = "9V54321D"))If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Hi Gaz_Chapp ,
Not very clear. Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Best Regards,
Eyelyn Qin
Thanks Eyelyn,
I have a single table with multiple columns. One column contains many Terminal ID numbers, up to 100 unique ID's. These ID's can however apply to multiple rows, with only around 30 of the ID numbers being relevant to my report. So, I want to filter out all of the unwanted ID's in the column to exclude them from the table. I have done this by using the text filter feature on the column to include only the ID numbers I want. However it is very slow to add each ID number using the the column text filter, as I have to select each field one by one using the "Table name, table column, Contains = 9V123456D" filter. As you will know this has to be done one line at a time in the text filter tool.
I was hoping to be able to use a measure to achieve the same result, so that when new terminal ID's are added to the database I can simply update the measure with the new ID's, which only change occasionally. What I did for now was to add a single text filter to the column using the tool, then using the query editor, edited the step manually to add the other ID numbers. This was just a quick way of adding to the text filter without having to use the tool line by line. Hope this all makes sense!
Thanks. Garry.
Hi Gaz_Chapp
Try this measure to create a new table by DAX code:
Filtered Table = filter('Table','Table'[TransAtmEftposId] in {"9V12345D","9V54321D"})Original Table :
Filtered Table:
In Power Query add click on "Insert Step After" and use this code [add new codes with OR at the end]:
= Table.SelectRows(#"Changed Type", each ([TransAtmEftposId] = "9V12345D" or [TransAtmEftposId] = "9V54321D"))If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Thanks Vahid, sorry for the late reply, I have been away this week on leave. I tried both examples but am unsure if you intended them to be two independant ways of applying the solution, or if you are suggesting I use both at the same time, i.e. create the new table AND apply the new filter step?
Using the second example of your code in a new step is basically what I did myself when manually adding new ID's, and that works fine without adding a new filtered table. Also, if I do create the filtered table I am assuming that I should then use the TransAtmEftposId column in the visualisation to filter out the required ID's?
Lastly, I need to add a relationship between the existing and new tables otherwise Power BI throws an error regarding the table relationship, and when I do create a relationship I receive a cardinality warning that it is a "many to many" relationship, is that OK to do? Thanks, Garry.
