topic DAX Returning thousands of unrelated rows when trying to removefilters in DAX Commands and Tips

DAX Returning thousands of unrelated rows when trying to removefilters

zabman — Tue, 27 Aug 2024 05:31:12 GMT

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

Re: DAX Returning thousands of unrelated rows when trying to removefilters

lbendlin — Tue, 27 Aug 2024 17:25:18 GMT

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

Re: DAX Returning thousands of unrelated rows when trying to removefilters

zabman — Tue, 27 Aug 2024 22:05:48 GMT

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

Re: DAX Returning thousands of unrelated rows when trying to removefilters

lbendlin — Tue, 27 Aug 2024 22:22:27 GMT

Use DAXDEBUGOUTPUT and EVALUATEANDLOG to see where it goes wonky.

And read that article I linked to.