Forum Discussion
Helper VFilter based on two conditions
I'm trying to return values in a Matrix where Orders associated with an Account that were invoiced last week but also show Open orders (i.e. not invoiced). I am able to create this Matrix but once I apply a date filter on the invoiced order, the open orders disappear because there is no invoice date. I suspect I will need to create a calculated table to achieve this but I am not sure of the DAX expression to do so.
e.g.
Account 1
Order 1 Invoice date (last week)
Order 2 Open (no invoice date)
Account 2
Order 1 Invoice date (last week)
Order 2 Open (no invoice date)
11 Replies
richbenmintzResident Rockstar
Hi jdubs,
Can you please provide either a sample pbix file or sample data along with the expected results as a table or image.
Thanks,
- amitchandak
Super User
jdubs , Join Invoice date with date table. For last week refer my blog.
When the invoice date is null that will also come. Power bi by default makes right join for one to many.
just use filter
Invoiced = calculate(count(Table[Invoice ID]), not(isbalank(Table[Invoice date]))) // use last week on this
open = calculate(count(Table[Invoice ID]), isbalank(Table[Invoice date]))not visualize this with calendar date/month/qtr/year
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123 - DataZoe
Microsoft Employee
jdubs The filter pane should be able to take two conditions on the same field, but you won't be able to select a relative date such as last 7 days.
You can alteratively create a measure or calculated column to "flag" rows with the multiple conditions met and then filter the matrix with it instead.
for a calculated column:
OpenOrLast7DaysOrders=if(or(isblank([Invoice Date]),datediff(today(),[Invoice Date],DAY)>=-7),1,0)
for measure (if the table is large this is a more performant option but you will need to have Invoice Date in the table):
OpenOrLast7DaysOrders=if(or(isblank(min(Orders[Invoice Date])),datediff(today(),min(Orders[Invoice Date]),DAY)>=-7),1,0)
jdubsThanks guys. I'll give this a shot and let you know how I make out.
- jdubs
This works but I only want to see the Open orders that are associated with the same Account as the Invoiced orders. This gives me ALL open orders in the matrix.
It is a view basically to see if there are any outstanding open orders on an Acount that has invoiced orders.
- jdubs
Tried going a different route and tried to merge the two queries (Inoiced and Open orders) into one table, using a full outer join on the Account ID but it is only returning the Invoiced orders (aka the ones with the Invoice Data value). Why wouldn't it also show me the Open orders?
- jdubs
When I use this formula: OpenOrLast7DaysOrders=if(or(isblank([Invoice Date]),datediff(today(),[Invoice Date],DAY)>=-7),1,0)
I get an error telling me that a "single value cannot be determined."
daxCommunity Support
Hi jdubs ,
I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
