Date Range Filter

[Anonymous]

Fact is populated with DateKey and Vendor Key for every submission of data file.

Example:

fctSales

fctsk        DateKey      VendorKey      Measure1   Measure2

1              20010101    101                 122             555

2              20010109    102                 522             565

3              20010111    101                 122             555

4              20010112    103                 592             123

dimVendor

VendorKey    VendorName

101               Vendor 101

102               Vendor 102

103               Vendor 103

104               Vendor 104

I want to create report to with date range filter to see which vendor has submitted data file between those dates.

Example if my selection dates are 20010101 to 20010112 

First Dataset:  should list Vendor 101, 102, 103 as data submitted

Second Dataset:  should list Vendor 104 as data NOT submitted

Relationship between fact and Vendor exists with direction of 'Both' and

Relationship between fact and Date exists with director of many to one.

What is the best way to acheive this?

[v-lili6-msft]

hi, @Anonymous

here is pbix file that Relationship between fact and Vendor exists with direction of 'Single', please try it.

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi, @Anonymous

After my research, you could try these two way as below:

1. for Relationship between fact and Vendor exists with direction of 'Both'

If you filter fctSales table, dimVendor will be filtered too, so you need a new dimVendor table and do not create a relationship with fctSales table.

Step1:

Create a new table by this formula

new dimVendor = dimVendor

Step2:

create a measure by this formula

NOT submitted = var _exsitVendorkey =VALUES(dimVendor[VendorKey]) return
CALCULATE(MAX('new dimVendor'[VendorName]),FILTER('new dimVendor',NOT( 'new dimVendor'[VendorKey]) IN _exsitVendorkey ))

Step3:

Drag VendorKey and VendorName from dimVendor table as Submitted Vendor

Drag VendorKey and VendorName and measure NOT submitted from new dimVendor table as Not Submitted Vendor

Result:

2. If you set Relationship between fact and Vendor exists with direction of 'Single'

When you filter fctSales table, dimVendor will be not filtered.

So you could try this way:

Create two measure

submitted = var _exsitVendorkey =VALUES(fctSales[VendorKey]) return
CALCULATE(MAX('dimVendor'[VendorName]),FILTER('dimVendor','dimVendor'[VendorKey] IN _exsitVendorkey ))

Not submitted = var _exsitVendorkey =VALUES(fctSales[VendorKey]) return
CALCULATE(MAX('dimVendor'[VendorName]),FILTER('dimVendor',NOT('dimVendor'[VendorKey]) IN _exsitVendorkey ))

Then Drag VendorKey and VendorName and two measure from dimVendor table

Result:

here is pbix file that Relationship between fact and Vendor exists with direction of 'Both', please try it.

Best Regards,

Lin