Same Formula, different results

[ShazzaGreen]

Hi all,

I have had to rebuild one of my reports due to some load issues and modelling enhancements. When I created my new report there are some calculated columns using DAX formula that I have copied exactly from the old report. For some reason, in the new model, they are returning no results. In the original model the formula worked perfectly. I have checked the formatting of all relevant columns and they're all formatted as text. Can anyone advise if I am missing something obvious or suggestions of what else I can check? Or maybe even another formula I could use to achieve the same results?

Basically I need the column in my BDoD Program table to return a count of rows from the BDoD QA table where the Project Id matches plus Audit Outcome = Major and Project Type = Design.

D_Major Errors =

CALCULATE

(COUNTROWS(

FILTER('BDoD QA','BDoD QA'[Audit Outcome]="Major")),

FILTER('BDoD QA','BDoD QA'[Project Type]="Design"),

ALL('BDoD QA'),'BDoD QA'[Project ID] = EARLIER('BDoD Program'[Project ID]))

[PhilipTreacy]

Hi @ShazzaGreen 

Try this

D_Major Errors =

CALCULATE( COUNTROWS('BDoD QA'), 

FILTER(ALL('BDoD QA'),'BDoD QA'[Audit Outcome]="Major" && 'BDoD QA'[Project Type]="Design" && 'BDoD QA'[Project ID] = EARLIER('BDoD Program'[Project ID])))

If that doesn't work please share your data/PBIX file and your expected results.

Regards

Phil

[ShazzaGreen]

Hi Phil,

Thanks so much for your response! The formula you've provided does exactly what I need it to in my original model and replicates the results I was getting with the original formula. Unfortuantely, it also returns just blanks cells in my new model (as with my formula). I would love to share the file but I work for an international company that subcontracts to another large company. The data in the file is confidential so I can't share it. There are 13 tables in the model, some of which connect directly to SharePoint lists for their data, so I don't know how I can 'cleanse' it with dummy data in order to share it with you?? 

You are spot on with the expected result already, it just doesn't return any result in the new model. There are no formula errors or anything showing, it's like it's just not finding any data meeting the formula criteria... but I know it's there...??

There is no direct relationship between the two tables, but they are indirectly related through a complex web of other related tables (including a date table).

It probably doesn't help much but I've added some screen shots of the old and new model showing formula results and the data relationships.

New Model ResultNew Model RelationshipsOld Model ResultOld Model Relationships