Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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?
Solved! Go to Solution.
@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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGlad 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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAfter 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 🙂
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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |