DAx not working. Userelationship and lookup together

[NandinAna]

Hi ,

The following dax was working so far,but now all of the sudden this wont work and not throwing error as well

FIRM_SellingPrice =

var firm_userprincipalname = [MF_USERPRINCIPALNAME]

RETURN

CALCULATE ( AVERAGE(IMPORT_SQL_monthly[SellingPrice] ),OR(IMPORT_SQL_monthly[CurrentStatus]="Sold", IMPORT_SQL_monthly[CurrentStatus]="SaleComplete"), TBL_CALENDAR_periods[Value] = "This year", LOOKUP_MF_USERS[PrincipalName] = firm_userprincipalname , IMPORT_SQL_monthly[PartShare] = 0, USERELATIONSHIP( IMPORT_SQL_monthly[DateSold], TBL_CALENDAR_date[Date] ), USERELATIONSHIP( IMPORT_SQL_monthly[MemberFirm], LOOKUP_MF_USERS[firm]) )       + 0

So when i spilt to look at where the issue is,

Part 1:This part as separate it works

CALCULATE ( AVERAGE(IMPORT_SQL_monthly[SellingPrice] ),OR(IMPORT_SQL_monthly[CurrentStatus]="Sold", IMPORT_SQL_monthly[CurrentStatus]="SaleComplete"), TBL_CALENDAR_periods[Value] = "This year" , IMPORT_SQL_monthly[PartShare] = 0, USERELATIONSHIP( IMPORT_SQL_monthly[DateSold], TBL_CALENDAR_date[Date] ), USERELATIONSHIP( IMPORT_SQL_monthly[MemberFirm], LOOKUP_MF_USERS[firm]) )       + 0

Part 2:This part as separate it works

.Avg_FIRM_SellingPrice =

var firm_userprincipalname = [MF_USERPRINCIPALNAME]

RETURN

CALCULATE ( AVERAGE(IMPORT_SQL_monthly[SellingPrice] ),OR(IMPORT_SQL_monthly[CurrentStatus]="Sold", IMPORT_SQL_monthly[CurrentStatus]="SaleComplete"), TBL_CALENDAR_periods[Value] = "This year", LOOKUP_MF_USERS[PrincipalName] = firm_userprincipalname , IMPORT_SQL_monthly[PartShare] = 0, USERELATIONSHIP( IMPORT_SQL_monthly[DateSold], TBL_CALENDAR_date[Date] ) )       + 0

so if both the USERELATIONSHIP( IMPORT_SQL_monthly[MemberFirm], LOOKUP_MF_USERS[firm]) and LOOKUP_MF_USERS[PrincipalName] = firm_userprincipalname combination (the first dax) does not work anymore.

Please could someone help me on this.

Thanks

[NandinAna · Accepted solution]

Hi There,

Thanks for your reply. I drilled down further and it was due to access issue.So its fixed now.

[daxer-almighty]

Hi @NandinAna 

What do you mean "not throwing an error"? If it's not throwing an error, it means the formula does what you told it to. The problem is, and I'm 99.9999% sure, with your DAX, not with PBI. The formula returns what you instructed it to. There is no other way. If it does not return what you expect, then it means you should debug the formula first of all and understand thoroughly how it works.

I don't think anyone could help you with this here since we don't know what you expect and what the formula returns. We don't know the model, either. If you've changed anything in the model between "it worked" and "now it does not", then the change might have impacted the code and hence it returns something different.