Forum Discussion

renmello's avatar
renmello
Helper I
6 years ago
Solved

Problem with calendar table

Hello!

I have a calendar table that i used for the costs from a table of projects  and it worked. 

But now i am trying without success to use the same calendar table for another table of tasks to calculate how many tasks i have by finnish date each month.


I've done the following:

Calculate the number of tasks: 

       I created a column to count the rows of tasks: Contagem entregas = if(Tarefas[TarefaÉMarco]=true,1,0)

        Then i used a sum measure to know how many tasks i have: Qtde Tarefas = Sum(Tarefas[Contagem entregas])
The sum of tasks returned is 500

 

For the task table i have the finnish date , i used the finnish date to relate to the date of the calendar table. When i check the table using the date from the calendar date it returns the sum of tasks every month:

 

 

 

 

 

 

 

 

But when i use the finnish date from the task table, without the relationship from the calendar date, it returns the right values for each month.

 

What would i be missing with the calendar table?

 

  • edhans's avatar
    edhans
    6 years ago

    renmello post a picture of your overall model. I'd bet money you have a bi-directional or many-to-many relationship(s) that isn't necessary nor desirable.

     

    You can still activate inactive relationships in measures inside of CALCULATE:

    Measure Name = 
CALCULATE(
    SUM(Table[Field]),
    USERELATIONSHIP(Table1[Date],Date[Date])
)

    For that measure only it will activate the relationship and force that path, removing ambiguity. 

    But I'd still like to see a screenshot of your model.

    Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
    Microsoft Guidance on Many-To-Many Relationships
    Microsoft Guidance on Bi-Directional Relationships
    Microsoft Guidance on Importance of Star Schema

  • edhans's avatar
    edhans
    6 years ago

    Glad to assist renmello - can you please mark one of the posts as a solution so this thread can be known to be solved and possibly assist others when they search in the future?

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    renmello - This looks for all the world like you are missing a relationship between your calendar table and your tasks table or that the relationship direction is not correct (from calendar to tasks). Click the 3rd icon on the left hand side and check to make sure your relationship is correct.

     

    • renmello's avatar
      renmello
      Helper I
      6 years ago

      Thanks for replying. 

      Well, the relationship was there, but it was not active. 

      When i try to activate it tells me that i cant do it because it would create an ambiguity between the calendar table and the projects table that i am using to know the costs by month.

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        renmello post a picture of your overall model. I'd bet money you have a bi-directional or many-to-many relationship(s) that isn't necessary nor desirable.

         

        You can still activate inactive relationships in measures inside of CALCULATE:

        Measure Name = 
CALCULATE(
    SUM(Table[Field]),
    USERELATIONSHIP(Table1[Date],Date[Date])
)

        For that measure only it will activate the relationship and force that path, removing ambiguity. 

        But I'd still like to see a screenshot of your model.

        Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
        Microsoft Guidance on Many-To-Many Relationships
        Microsoft Guidance on Bi-Directional Relationships
        Microsoft Guidance on Importance of Star Schema