Filter based on two conditions

[jdubs]

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)

[dax]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataZoe]

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

[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."

[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?

[dax]

Hi @jdubs , 

We couldn't reproduce your problem based on your description, 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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdubs]

@dax Hi, i actually have a another requirement that might be easier to explain as I never made it very far with the last one. It is very similiar. I want to show a table of Accounts who have Lost Opportunities in Dynamics 365 in the last month, but in that same table (or matrix) I also want to show any Open opportunities that they still have. The problem that I have is that when I add the Open Opportunites to the table it shows ALL Open Opportunities and not just the ones associated with the Accounts that had Lost Opportunities within the given time frame. 

[jdubs]

This would be a sample scenario

Table 1 - Accounts

Table 2 - Opportunities (w/ relationship to Accounts)

Expected output (Matrix):

List of Accounts who Lost Opportunities this month, showing any additional associated Opportunties

Account	Opportunity Status	Opportunity Name	Date Closed (filtered by This Month)
Account 1	Lost	Lost Opportunity 1	8/2/20
	Open	Open Opportunity 1
		Open Opportunity 2
Account 2	Lost	Lost Opportunity 2	8/3/20
	Open	Open Opportuntiy 3
	Won	Won Opportunity 1	8/4/20

[jdubs]

Thanks guys. I'll give this a shot and let you know how I make out.

[amitchandak]

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

[richbenmintz]

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,