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...
  • 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?

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

      • renmello's avatar
        renmello
        Helper I
        6 years ago

        After reading what you shared i realized i had one fact table (costs for each task) and two dimension tables (the calendar table and the tasks table) and i was trying to create a relationship between two dimension tables (the calendar table and the tasks table). 

        What i did was use the dax function related to bring the finnish date and the actual finnish date from the tasks table and i maintained the relationship from the calendar table to the costs table. Here is a print of how it is now. 

         

        Now i am able to know how many tasks i have to finnish each month  and it is working with the costs when in the dashboard. But i am not sure if i found out the right solution. 

        Anyway, thanks everyone for the reply and thanks edhans for the knowledge shared. I am very new to power bi and data modelling so i appreciate the help 🙂