Forum Discussion
Measure to filter only required column values
Hi all, new to this and it's driving me crazy.....
I have a table column that contains many unique terminal ID numbers. I want to create a measure that shows (filters) only the specific ID's I need for my matrix. i.e. A123456D, A123454D etc. etc. and not display the rest. This would be similar to the "IN" function of TSQL where I can list only the values I want returned in the select statement. Hope that makes sense!
I can do this manully in data view using text filters, but it is very tedious to go through and find/select only the ID's I want to keep. With a measure I could copy & paste the entire list if it ever needs to be updated. Any guidance and examples would be much appreciated. I have undertaken several online DAX courses but this doesn't appear to be detailed anywhere in the courses and I am still very new to DAX.
Much appreciated. 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✌️!!
6 Replies
- VahidDM
Super User
Hi Gaz_Chapp
You can use the filter DAX like these:
New Table = filter(table,table[terminal ID ] = "Specific ID")
Or
New Table = filter(table,table[terminal ID ] in {ID1, ID2,ID3,...})
And you can use these codes in the Calculation.If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos ✌️!!
Thanks for the reply. Unfortunately the solution did not work for me. I used a text filter and edited it manually to include the additional 30+ ID's and it worked. Not sure what it is about the measure but it doesn't like the expression, I get an error "The Expression Refers to Multiple Columns". Thanks.
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 QinThanks 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.
- VahidDM
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✌️!!
