cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dw700d
Helper V
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 OrderAccount Code
1ABC
1DEF
3DEF
5PQR
5STU
5WXY
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

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





View solution in original post

5 REPLIES 5
parry2k
Super User
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] )

 

parry2k_0-1627162823187.png

 

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.

 

 

 

 






Did I answer your question? Mark my post as a solution.

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





parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

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





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

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






Did I answer your question? Mark my post as a solution.

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





selimovd
Super User
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
 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors