cancel
Showing results for
Did you mean:
Helper V

## 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
1 ACCEPTED SOLUTION
Super User

@dw700d solution is attached, look at PO and Account Code tables, ignore other tables in the file.

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

5 REPLIES 5
Super User

@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

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@dw700d solution is attached, look at PO and Account Code tables, ignore other tables in the file.

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper V

@parry2k  Thank you for the help. are you able to show screenshots? im unable to download your dashboard due to restrictions on my laptop

Super User

@selimovd 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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

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 regards
Denis