Forum Discussion
Date Range Filter
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?
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
2 Replies
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
hi, Anonymous
here is pbix file that Relationship between fact and Vendor exists with direction of 'Single', please try it.
Best Regards,
Lin