Forum Discussion

CRBam's avatar
CRBam
Frequent Visitor
8 years ago

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-msft's avatar
    v-caliao-msft
    Microsoft Employee

    CRBam,

     

    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

    • CRBam's avatar
      CRBam
      Frequent 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 NumberQuote SSR DateQuote SSR Date KeyCompleted SSR DateCompleted SSR Date KeyQuotes StartedPolicies SoldBlended Quote PremiumBlended Quote CommissionBlended Sold PremiumBlended Sold CommissionUniq Line IDUniq Policy IDUniq OriginalPolicy ID
      A10501/31/20162016013102/12/201620160212115560556111.2656166561665615
      A10601/31/20162016013103/22/201620160322111611201.381611201.38698186981769815
      A10706/30/20162016063006/30/201620160630112108263.52108263.533842833827978124
      A10802/12/20162016021202/12/201620160212112572321.52572321.5196657196546196546
      A10901/31/20162016013102/10/201620160210116423.57562.982286285.76783997837778375
      A1111/22/20162016112212/23/2016NULL100000106532510649411064941
      A11801/08/20172017010801/08/20172017010811652130.4652130.41111814111137378398
      A1205/19/20162016051905/19/20162016051911000021262521250475061
      A1211/26/201620161126NULLNULL104668000106895110685591068559
      A12008/26/20162016082608/26/201620160826111109221.81109221.836450836431474171
      A1309/12/20162016091209/12/2016201609121132464.832464.837078737058866350
      A1311/24/20162016112412/15/2016NULL104803000106757110671821067182
      A13004/26/20162016042604/26/201620160426111077.9901839229.8820776320764466027
      A13101/31/20162016013101/31/201620160131112191000697696976869767
      A13206/09/20172017060906/09/2017201706091163979.8863979.881773689177298465866