Forum Discussion
Bug in my model referenceing two different date tables
Hi, I have been trying to use one date table with two different dates, and found some solutions on here which is great - thanks! However, it still isn't working for me, even though it looks like it should. Hopefully someone can spot what I am doing wrong, whether in model set up or DAX. I canot upload the PBIX for privacy reasons....
Snip of affected tables;
Linking on Quote DateKey (Integer) = Reporting Date DateKey (Integer) (Active 1:1 Cross filter Both)
and
Linking on Sale Completed DateKey (Integer) = Reporting Date DateKey (Integer) (InActive 1:1 Cross filter Both) - cannot make active due to circular reference.
Also Linking on Quote DateKey = Policy Information Quote DateKey (Active 1:Many Cross Filter Both)
and
Linking on Sale Completed DateKey = Policy Information Sale DateKey (Active 1:Many Cross Filter Both)
(I also tried with the date field, same results)
Quote DAX - Seems to work:
Quote Policies # = Calculate(
SUMX('Policy Information', 'Policy Information'[Quotes Started]),
Filter (Values ( 'Quote Date'[DateKey] ), 'Quote Date'[DateKey] >= Min ( 'Reporting Date'[DateKey] ) ),
Filter (Values ( 'Quote Date'[DateKey]), 'Quote Date'[DateKey] <= MAX ( 'Reporting Date'[DateKey] ) ))
Sales DAX - Not Working:
Sales Policies # = Calculate(
SUMX('Policy Information', 'Policy Information'[Policies Sold]),
Filter (All ( 'Sale Completed Date'[DateKey] ), 'Sale Completed Date'[DateKey] >= Min ( 'Reporting Date'[DateKey] ) ),
Filter (All ( 'Sale Completed Date'[DateKey]), 'Sale Completed Date'[DateKey] <= MAX ( 'Reporting Date'[DateKey] ) ))
I expect to see 199 Quotes and 93 sales but instead I see:
Please help, and let me know if I can provide any other information to help solve this conundrum...thanks!
2 Replies
- v-caliao-msftMicrosoft Employee
Quote DAX - Seems to work:
Quote Policies # = Calculate(
SUMX('Policy Information', 'Policy Information'[Quotes Started]),
Filter (Values ( 'Quote Date'[DateKey] ), 'Quote Date'[DateKey] >= Min ( 'Reporting Date'[DateKey] ) ),
Filter (Values ( 'Quote Date'[DateKey]), 'Quote Date'[DateKey] <= MAX ( 'Reporting Date'[DateKey] ) ))Sales DAX - Not Working:
Sales Policies # = Calculate(
SUMX('Policy Information', 'Policy Information'[Policies Sold]),
Filter (All ( 'Sale Completed Date'[DateKey] ), 'Sale Completed Date'[DateKey] >= Min ( 'Reporting Date'[DateKey] ) ),
Filter (All ( 'Sale Completed Date'[DateKey]), 'Sale Completed Date'[DateKey] <= MAX ( 'Reporting Date'[DateKey] ) ))You used ALL function in your policies measure, this function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. And the result still less than you expected result. it would be better if you could provide us some sample data, so that we can make further analysis.
Regards,
Charlie Liao
- CRBamFrequent Visitor
HI, yes I think putting 'ALL' in was a hail mary attepmt to get the sales to show up. :) I have some sample data I can upload....just need to figure out how to do it.
I'm back, I just don't see how to attach or upload a file, so here is a small sample of the data (not all columns or rows):
Policy Number Quote SSR Date Quote SSR Date Key Completed SSR Date Completed SSR Date Key Quotes Started Policies Sold Blended Quote Premium Blended Quote Commission Blended Sold Premium Blended Sold Commission Uniq Line ID Uniq Policy ID Uniq OriginalPolicy ID A105 01/31/2016 20160131 02/12/2016 20160212 1 1 556 0 556 111.2 65616 65616 65615 A106 01/31/2016 20160131 03/22/2016 20160322 1 1 1611 201.38 1611 201.38 69818 69817 69815 A107 06/30/2016 20160630 06/30/2016 20160630 1 1 2108 263.5 2108 263.5 338428 338279 78124 A108 02/12/2016 20160212 02/12/2016 20160212 1 1 2572 321.5 2572 321.5 196657 196546 196546 A109 01/31/2016 20160131 02/10/2016 20160210 1 1 6423.57 562.98 2286 285.76 78399 78377 78375 A11 11/22/2016 20161122 12/23/2016 NULL 1 0 0 0 0 0 1065325 1064941 1064941 A118 01/08/2017 20170108 01/08/2017 20170108 1 1 652 130.4 652 130.4 1111814 1111373 78398 A12 05/19/2016 20160519 05/19/2016 20160519 1 1 0 0 0 0 212625 212504 75061 A12 11/26/2016 20161126 NULL NULL 1 0 4668 0 0 0 1068951 1068559 1068559 A120 08/26/2016 20160826 08/26/2016 20160826 1 1 1109 221.8 1109 221.8 364508 364314 74171 A13 09/12/2016 20160912 09/12/2016 20160912 1 1 324 64.8 324 64.8 370787 370588 66350 A13 11/24/2016 20161124 12/15/2016 NULL 1 0 4803 0 0 0 1067571 1067182 1067182 A130 04/26/2016 20160426 04/26/2016 20160426 1 1 1077.99 0 1839 229.88 207763 207644 66027 A131 01/31/2016 20160131 01/31/2016 20160131 1 1 2191 0 0 0 69769 69768 69767 A132 06/09/2017 20170609 06/09/2017 20170609 1 1 639 79.88 639 79.88 1773689 1772984 65866