Forum Discussion
using a measure to filter specific items
I have a table with thousands of purchase orders .Each purchase order has multiple account codes. I would like to create a measure that only shows purchase orders that contain account codes DEF or WXY.
The original table is below
Purchase Order | Account Code |
1 | ABC |
1 | DEF |
2 | ABC |
2 | GHI |
3 | DEF |
4 | JKL |
4 | MNO |
5 | PQR |
5 | STU |
5 | WXY |
The new table with measure would only show Purchase order 1, 3 &5 because these orders contain DEF& WXY
| Purchase Order | Account Code |
| 1 | ABC |
| 1 | DEF |
| 3 | DEF |
| 5 | PQR |
| 5 | STU |
| 5 | WXY |
dw700d solution is attached, look at PO and Account Code tables, ignore other tables in the file.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
5 Replies
Hey dw700d ,
why don't you just use a slicer:
Slicers in Power BI - Power BI | Microsoft Docs
Or the filter in the filter pane:
Format filters in Power BI reports - Power BI | Microsoft Docs
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicselimovd I don't think slicer and filter will work, I believe dw700d want to see all the rows that contains selected account code in this case "DEF" and "WXY"
dw700d You need to create disconnected tables for account code, use that for the slicer, and then use a measure to find out which PO contains that selected accounts code and then only shoe those ones.
dw700d solution is attached, look at PO and Account Code tables, ignore other tables in the file.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
dw700d here are the steps to get it done:
first, create an Account Code table to be used in the slicer by using the following DAX expression. Change PO to the table name in your model.
Account Code = ALLNOBLANKROW ( PO[Account Code] )2nd, add a following measure to filter the POs
Filter PO = CALCULATE ( COUNTROWS ( PO ), TREATAS ( VALUES ( 'Account Code'[Account Code] ), 'PO'[Account Code] ) )1 - Add a slicer with account code from the table created in step 1
2 - add a table visual, put PO and Account code from PO table
3 - add a visual level filter using the Filter PO measure
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
