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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
johankent30
Helper I
Helper I

Measure Filter Help

I have the following measure:
Measure 2 = IF(MAX(Collections1[Subcategory])="Sales Orders",'Sales Orders'[Sales Orders Value],
IF(MAX(Collections1[Subcategory])="Purchase Orders",'Purchase Orders'[Purchase Orders Value],
[Measure]+0))
 
I want to be able to filter a subcategory by a date filter. The date filter needs to be from a separate date table. Ideally, the formula would like like the code below, however this does not work. Does anyone know of a way to do this?
 
Measure 2 = IF(MAX(Collections1[Subcategory])="Sales Orders",'Sales Orders'[Sales Orders Value],
IF(MAX(Collections1[Subcategory])="Purchase Orders",CALCULATE('Purchase Orders'[Purchase Orders Value],Collections1[Date Value]=Date[Date]),
[Measure]+0)))
6 REPLIES 6
parry2k
Super User
Super User

@johankent30 can you share how you tables are related?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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 right now there is now relationship. I’m not opposed to creating one. If I did it would be a many to one relationship from the collections1 table to the date table

@johankent30 as best practice it will be easier to have relationship. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Okay, is it possible to filter the subcategory values by a date selected on the date table then? How would the DAX look in my formula?

@johankent30 share sample data from tables you currently have and explain what you want. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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 I've tried to include more info, but let me know if you need anything else. I have the following visual.8.12.JPG

You can see that I am analyzing the AP data by a date heiarchy. The table that feeds this visual looks like this.8.122.JPG

 

What I am hoping to do is to create a date slicer that can filter just the subcategory of "Purchase Orders". So referring back to the first pic attached, the purchase orders amount is $1399 for each day, but I want to be able to select dates from a slicer list and choose which days the purchase orders show up in the visual. So if I selected 9/1/2019 then purchase orders would have $1399 on 9/1/2019 and 0 on all of the other days, but the Payables line would not change. The measure that I believe needs to be changed is in the second pic. I would think there would be a way to filter the Date Value column in the table illustrated above for only the dates selected in a disconnected date table, and for this to only apply if the row had "Purchase Orders" as the subcategory, but I can't seem to figure out the solution. Thanks!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors