Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
renmello
Helper I
Helper I

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:
Capturar.PNG

 

 

 

 

 

 

 

 

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.

Capturar.PNG

 

What would i be missing with the calendar table?

 

2 ACCEPTED SOLUTIONS

@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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@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.

Greg_Deckler_0-1598645065945.png

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

@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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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. 

RelationShips.jpg

 

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 🙂 

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors