cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User IV
Super User IV

Re: Measure Filter Help

@johankent30 can you share how you tables are related?






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.





Highlighted
Helper I
Helper I

Re: Measure Filter Help

@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
Super User IV
Super User IV

Re: Measure Filter Help

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






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.





Highlighted
Helper I
Helper I

Re: Measure Filter Help

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?
Highlighted
Super User IV
Super User IV

Re: Measure Filter Help

@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






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.





Highlighted
Helper I
Helper I

Re: Measure Filter Help

@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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors