DAX Returning thousands of unrelated rows when trying to removefilters

[zabman]

Hi,

I have been trying to get my head around some DAX related to Azure DevOps for the past few hours and I am in need of help.

I have got the DAX to return correctly but it also returns hundreds of thousands of rows that I do not expect, and where there is no relationship.

I want the DAX to report the total project hours irrelevent of other filters (except for the project itself)

The DAX in question is:

	MEASURE UserStories_Tickets[HoursCompleted] = 
		SUM(Tasks[CompletedWork])
	
    MEASURE 'Projects'[Total_Hours] = 
		CALCULATE (
			[HoursCompleted] ,
			Projects
			,ALL(Tasks)
			,ALL(Task_Update_Calendar)
		)

When this is presented in a table with fields from Project I get results as expected. But as soon as I add the title from the UserStories_Tickets table I get thousands of rows reporting.

I suspect this is a result of the Auto-Exist behaviour but I cannot resolve it. I have tried what seems like hundreds of different filters in the measure but whenever I get the correct 'hours' at a project level, it also creates the issue with thousands of non related rows.

My tables are connected as follows:

The calendar is on the Task Revisions because I visualize the data per the week the task hours are updated, which can be split over multiple weeks per task.

The result should look like the following

Project[Title]	UserStories_Tickets[Title]	Task_Update_Calendar[Year_Week]	UserStories_Tickets[HoursCompleted]	Projects[Total_Hours]
ABC	123	2023_41	7	14.5
ABC	456	2023_41	3.5	14.5
ABC	456	2023_42	4	14.5
DEF	123	2023_41	1	3
DEF	456	2023_41	2	3
GHI	123	2023_41	8	8

[lbendlin]

MEASURE 'Projects'[Total_Hours] = 
		CALCULATE (
			[HoursCompleted] ,
			Projects
			,ALL(Tasks)
			,ALL(Task_Update_Calendar)
		)

What's the purpose of including Projects? Not only are you filtering an entire table, but you are filtering  its expanded table too. Filter columns, not tables, in DAX - SQLBI

[zabman]

Hi - to be completely honest I don't know. This was one of the few ways I was able to get this to give me the correct result. When I remove this, I get memory issues and the dax does not resolve

[lbendlin · Accepted solution]

Use DAXDEBUGOUTPUT and EVALUATEANDLOG to see where it goes wonky.

And read that article I linked to.