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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gaz_Chapp
Frequent Visitor

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

1 ACCEPTED SOLUTION

Hi @Gaz_Chapp 

 

Try this measure to create a new table by DAX code:

Filtered Table = filter('Table','Table'[TransAtmEftposId] in {"9V12345D","9V54321D"})

VahidDM_0-1631249369588.png

Original Table :

VahidDM_1-1631249407345.png

 

Filtered Table:

 

VahidDM_2-1631249422921.png

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"))

VahidDM_3-1631249661402.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
Community Support

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.

Screenshot 2021-09-10 124739.png

 

 

 

 

 

 

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"})

VahidDM_0-1631249369588.png

Original Table :

VahidDM_1-1631249407345.png

 

Filtered Table:

 

VahidDM_2-1631249422921.png

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"))

VahidDM_3-1631249661402.png

 

 

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.

VahidDM
Super User
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.