DAX Works as a Calc Column but not as a Measure

[littlemojopuppy]

Hi!  Would like to ask for some help with something...

Need to calculate how long it's been since a Member was invited to a Program.  But my question isn't about that specifically...my question is why using DAX in a calculated column works for every case, but not a measure.

Code for the calculated column:

=VAR	InvitationDate =
	CALCULATE(
		FIRSTNONBLANK(
			ProgramInvitations[CreatedDate],
			TRUE()
		),
		TREATAS(
			VALUES(ProgramInvitations[OrganizationMemberId]),
			OrganizationMembers[Id]
		)
	)
RETURN
	
DATEDIFF(
	InvitationDate,
	TODAY(),
	DAY
)

Code for the measure:

Days Since Sending Invite:=
VAR	InvitationDate =
	CALCULATE(
		FIRSTNONBLANK(
			ProgramInvitations[CreatedDate],
			TRUE()
		),
		TREATAS(
			VALUES(ProgramInvitations[OrganizationMemberId]),
			OrganizationMembers[Id]
		)
	)
RETURN
	
DATEDIFF(
	InvitationDate,
	TODAY(),
	DAY
)

Screen snip of the table ProgramInvitations table that I need to get the date from...

And a screen snip of the output...the four highlighted records below are the four records shown above.


What I find very odd about the measure is that it is not able to calculate a value for records with a status of "pending" (also missing a Profile ID).  But the exact same code works as a calculated column.  The tables are not directly related (there is an inactive relationship) but are indirectly related through a couple other table between them.

I initially tried LOOKUPVALUE to retrieve the date but was getting the same results.  So I moved onto using FIRSTNONBLANK and TREATAS.

Any insight to why the measure doesn't work but the column does would be appreciated.  Would prefer to use the measure, assuming it works.  Thank you!  😊

[edhans]

Without having access to your PBIX file this is very difficult to figure out, but note that the measure is affected by the filters in the model, so filters from the visual, slicers, the filter pane, etc. Calculated columns are not. 

I agree that measures are the better way to go, but you are probalby going to have to override the implicit filters that are affecting the measure.

[littlemojopuppy]

The data model isn't in a PBIX file.  It's in a very large Analysis Services database filled with HIPAA data so I'm unable to share it.

The screen snip I posted was filtered by date range (1/1/2019 - today) and organization (for testing purposes).  I'm 99% certain no other filters, but will verify Monday.

I mentioned that I had tried LOOKUPVALUE...during trying to figure out why that didn't work I came across a post in this forum from someone who had a similar issue.  That person's issue was caused by having "incomplete" data which prevented the relationship from table A to table C through table B was missing in table B.  The ones that the measure won't calculate are missing Profile ID (which is the field the relationship to a table between is based on) is missing.  But using TREATAS is supposed to mimic a relationship when a direct one doesn't exist so that shouldn't matter.  And I would think that that would impair the calculated column more than the measure.  Maybe???  😀

[littlemojopuppy]

I just confirmed there are no filters other than the date range and organization

[edhans]

Ok, but those are filters that are not in a calculated column. Calculated columns have no implicit filters, ever. They cannot. But a measure always has to deal with those, which is usually a good thing, but in this case, you have to remove the filters coming from the model as applicable to get the measure to work properly. Someone else may be able to jump in and help, but it is hard to do without a PBIX file or model to work with. This is a Power BI forum and while SSAS is underneath, much of the product is obscured from Power BI users. You may want to ask this question in a forum that has SSAS users that would be used to dealing with questions and how to share models and data to assist in troubleshooting.

[littlemojopuppy · Accepted solution]

So I did a little bit more research and reworked the measure.  This produces exactly the intended outcome

Days Since Sending Invite:=
VAR	InvitationDate =
	FIRSTNONBLANK(
		ProgramInvitations[CreatedDate],
		SELECTEDVALUE(OrganizationMembers[Id])
	)
RETURN
	
DATEDIFF(
	InvitationDate,
	TODAY(),
	DAY
)

[littlemojopuppy]

The relevant part of the data model (between these two tables) is as follows:

• Organization Members related to Identities (1:M, bidirectional), on IdentityID
• Identities related to ProfileRelationships (1:M, bidirectional), on IdentityID
• ProfileRelationships to Profiles (M:1, bidirectional) on ProfileID
• Profiles to ProgramInvitations (1:M, uni directional(?)) on Profile ID

And as I mentioned there is an inactive relationship directly between Organization Members and ProgramInvitations on OrganizationMemberID.