Forum Discussion

Draginko's avatar
Draginko
Helper I
3 years ago
Solved

How to overcome error "Function 'DATEADD' expects a contiguous selection..." with bi-dir date table

I have dashboard with different views on email traffic analytics, where I have one visual displaying evolution of reads for first week, and in order to make it work I have a bi-directional relationship between date table and fact table, with second relationship from date table to dim_reads table, which is active only in one specific view. Here is my data model, most of it comes from ODATA transfer, with one dimensional excel stored at onedrive, and two tables which are generated in PQ (one of it is date table):


All charts, measures and different pages works fine, but now I am working on a trending view, where I will compare different quarters for different metrics, with possibilities to display per different tags (audience, group, communication topic, etc...). Here is the snippet of planned comparision, where user can select the quarter which they want to compare against previous quarter:

 

And as you can see, all is working well and calculations are correct. The problem starts, when I want to filter it by e.g. audience:

As you can see, all calculations are again correct, with the expection of displayed quarters, here is the full error message:

Here are the measures which are used for quarters:

This one is used in numeric calculations, and it is the quarter which will be selected by user:
_SelectedQrt =
CALCULATE(
    QUARTER(
        MAX('Calendar'[Date])
        )
    )  


And in the card visualisation bellow chart is taking another column from date table, which will display quarter-year in desired format.

Here is the measure for taking previous quarter of selected one, also used in calculations:

_PreviousQrt =
CALCULATE(
    (fct_SendMessages[_SelectedQrt]),
    DATEADD('Calendar'[Date], -1, QUARTER)
    ) 
 
And then another measure which is used for card vis:
_PreviousQrtKPI =
"Q" & [_PreviousQrt] & " '" & RIGHT(YEAR(MAX('Calendar'[Date])), 2)
 


Of course, this can be fixed by changing the bi-dir relationship from date table into single one, but then it will break whole logic on other page, which I dont want.

And here are my questions:

  1. Why it breaks only that quarter name measure, and not the numeric calculations, which are also using selected/previous quarters in their logic?
  2. How to fix this? Ideally, without changing that bi-dir, because then I have to rework quite a chunk of items and currently not sure how to achieve desired output without bi-dir. But given the fact, that all numeric calculations are working well, it shouldnt be necessary to change the relationship, and I hope it will be just some simple fix 😄 

Let me know, if you have questions or need some more information related to this, and I thank you in advance if you give this a thought, I am stucked with this already for two days.

  • Draginko's avatar
    Draginko
    3 years ago

    Thank you for your message NikhilChenna .

    However, in my case, due to the narrow timeframe of dataset, currently I have data only for this year, and in every situation I want to compare two following quarters, so they are indeed contiguous. 

    And also, based on this logic this should break both quarter names and numeric calculations.

     

    In the meantime I found one, not very elegant tho, solution, which after initial testing seems working fine.

    I have replaced the previously used measure for giving previous of the selected quarter:

    PreviousQrt =
    CALCULATE(
        (fct_SendMessages[SelectedQrt]),
        DATEADD('Calendar'[Date], -1, QUARTER)
        )
     
    And replaced with this measure:
    PreviousQrt_viaMax =
    CALCULATE(
        MAX('Calendar'[Quarter])
    ) -1

    As I have mentioned, not very elegant, but it seems it is doing the required job, so far all is good. I think we can close this thread.
     
    Thank you

4 Replies

  • Hi Draginko ,

     

    As the error says you can't have periods selected that are not contiguous to one another. So, if you select Jan and Feb in the same year it'll work, but if you select Jan and March, say, in the same year, it'll not work. 

     

    But to solve this i suggest to check the relationships once again the issue is there only.

     

    Regards,

    Nikhil Chenna

     

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

    • Draginko's avatar
      Draginko
      Helper I

      Thank you for your message NikhilChenna .

      However, in my case, due to the narrow timeframe of dataset, currently I have data only for this year, and in every situation I want to compare two following quarters, so they are indeed contiguous. 

      And also, based on this logic this should break both quarter names and numeric calculations.

       

      In the meantime I found one, not very elegant tho, solution, which after initial testing seems working fine.

      I have replaced the previously used measure for giving previous of the selected quarter:

      PreviousQrt =
      CALCULATE(
          (fct_SendMessages[SelectedQrt]),
          DATEADD('Calendar'[Date], -1, QUARTER)
          )
       
      And replaced with this measure:
      PreviousQrt_viaMax =
      CALCULATE(
          MAX('Calendar'[Quarter])
      ) -1

      As I have mentioned, not very elegant, but it seems it is doing the required job, so far all is good. I think we can close this thread.
       
      Thank you
      • NikhilChenna's avatar
        NikhilChenna
        Skilled Sharer

        Hi Draginko ,

        As the you said it seems it is doing the required job, so far all is good. I think we can close this thread.

         

        Appreciate with a Kudos!! (Click the Thumbs Up Button)
        Did I answer your question? Mark my post as a solution!

    • PBIDevFW's avatar
      PBIDevFW
      Frequent Visitor

      You won't get any kudos with responses like this. Why bother?