Filter visual by values of another column

[Aniket_PowerBi]

Hello All,

I have two dataentities:

1)ProductionOrderHeaders

Fields: ProductionOrderNumber, ScheduledStartDate, SONumber

2)SalesOrderHeaders

Fields: ConfirmedShippingDate, SONumber

I have applied date filter of ScheduledStartDate on my dashboard

and the requirement is I have to show line chart of count of ProductionOrderNumbers for each ConfirmedShippingDate.

When I merged these two data entities using SONumber field, the data of line chart is getting filter but it is filtering data by ScheduledStartDate. I want to filter my data by ConfirmedShippingDate since ConfirmedShipping Date is the field I am showing on line chart.

Any guide will be appriciated. Thanks!!

[Greg_Deckler]

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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[speedramps]

Hi Aniket

Please consider this solution:-

The problem:-

You have 2 tables:-

ProductionOrderHeaders

• SONumber
• ProductionOrderNumber
• ScheduledStartDate

SalesOrderHeaders

• SONumber
• ConfirmedShippingDate

Requirement

• ProductionOrderNumbers for each ConfirmedShippingDate.

Solution:-

• In query mode merge SalesOrderHeaders with ProductionOrderHeaders on SONumber using a left outer join.
• Expand the columns and remove the expanded ProductionOrderHeaders. SONumber
• Delete all relationships
• Hide the ProductionOrderHeaders because you dont need it now.

Leaving just SalesOrderHeaders with:-

• SONumber
• ConfirmedShippingDate
• ProductionOrderNumber
• ScheduledStartDate

Create visuals 

• Create a table visual with ConfirmedShippingDate and count of SONumber. Check figures.
• Create a line graph visual with axis = ConfirmedShippingDate and value = count of SONumber.

[Aniket_PowerBi]

Thank You for your reply.

I tried your solution but didnt get expected output.

In the date I have chose min date 5th Nov 2016, but in the below grid it is also showing me record for year 1900. It means this slicer not filtering data according to dates selected.

[speedramps]

Hi again Aniket

Did you try my solution or other one? There is always more than one way.

It sounds like you have rows with missing ConfirmedShippingDate which then default to 01 Jan 1990

You can remove these rows in the M query or drag the ConfirmedShippingDate to the filter well and exclude them there.

Incidentally, you dont need the relationship on your screen shot and can 

• Delete all relationships
• Hide the ProductionOrderHeaders because you dont need it once you have merge the needed columns into the other table.

[vanessafvg]

can you share a screenshot of your table relationships and are you using the slicer on scheduled data, please share more information

do you now have one merged table, or have you joined them?  are any of these tables linked to a date table?

[Aniket_PowerBi]

Hi,

Yes I have slicer of ScheduledStartDate.

I have merged two tables, ProductionOrderHeaders and SalesOrderHeaders => Merge1

Also this Merge1 is linked to ProductonOrderHeaders via SalesOrderNumber

[v-lili6-msft · Accepted solution]

hi @Aniket_PowerBi 

The relationship between two tables is based on [SalesOrderNumber], not date field, so when you filter ScheduledStartDate, it will filter Merge1 table by SalesOrderNumber not by ConfirmedShippingDate. 

ScheduledStartDate and ConfirmedShippingDate has no direct correspondence relationship, unless you create a relationship between two tables by ScheduledStartDate and ConfirmedShippingDate instead of SalesOrderNumber.

Regards,

Lin