Problem With Counting Records by Date

[littlemojopuppy]

Hi!  Would like to ask for some help if anyone is willing...

I have a table of Organizations and those Organizations have Members.  When an Org gets it's 11th Member is supposed to trigger invoicing for that Org.  I've created a measure for that previously, and it works correctly.  Now the business is asking to know the number of Orgs that hit their 11th Member by Date, so I need another measure.  Here's the code

Playing:=
VAR	DatesOf11thMember =
	FILTER(
		SELECTCOLUMNS(
			RootOrganizations,
			"Date11thMember",
			[Date of 11th Active Member]
		),
		NOT(ISBLANK([Date11thMember]))
	)
RETURN

CALCULATE(
	COUNTROWS(DatesOf11thMember),

	-- 1st Attempt
	--FILTER(
	--	DatesOf11thMember,
	--	[Date11thMember] = SELECTEDVALUE('Calendar'[Date])
	--)

	-- 2nd Attempt
	--VALUES('Calendar'[Date])

	-- 3rd Attempt
	--TREATAS(DatesOf11thMember, 'Calendar'[Date])

	--4th Attempt
	KEEPFILTERS('Calendar'[Date])
)

This is the output of the table variable in the code, and it's accurate

Unfortunately, this is the output of the measure

It appears that it's counting rows for dates <= the date in the table, and not for that date only.  What I would expect to see is a count of 1 on the two highlighted rows and blank for all others.  I've tried this four different ways (this morning...Friday had a couple different approaches and those also produced the same result).  I'm clearly missing something...

Could anyone provide a hint to what I'm doing wrong?  Thank you!

[littlemojopuppy · Accepted solution]

Ok...I've been working on this since Friday with no progress at all.  So I decided it's time to cheat 😞  I took created a calculated column on the Organizations table with a value calculated by the measure [Date of 11th Active Member].  I hate calculated columns.

From there, it became fairly easy to do this...

Organization Count By Date of 11th Member:=
VAR	CurrentDate = 
	FILTER(
		VALUES('Calendar'[Date]),
		NOT(ISBLANK('Calendar'[Date]))
	)
RETURN

CALCULATE(
	COUNTROWS(RootOrganizations),
	FILTER(
		RootOrganizations,
		RootOrganizations[DateOf11thMember] IN CurrentDate
	)
)

I found that using SELECTEDVALUE was causing higher levels in the date hierarchy to count all Orgs with no date for 11th member (because a single date isn't selected) so while the logic looks a little weird, it's producing exactly the intended result.

I think that the issue I was having was coming down to data lineage in DAX, but I'll be damned if I could figure out why it wasn't working.  If anyone could offer a hint on how this could have been accomplished without using calculated columns, I'd certainly appreciate the input.

@vivran22  thanks for trying to help!  😀

[vivran22]

Hello @littlemojopuppy ,

Try this:

Playing:=
VAR _CurDate = SELECTEDVALUE('Calendar'[Date])
VAR	DatesOf11thMember =
	FILTER(
		SELECTCOLUMNS(
			RootOrganizations,
			"Date11thMember",
			[Date of 11th Active Member]
		),
		NOT(ISBLANK([Date11thMember]))
	)
RETURN

CALCULATE(
	COUNTROWS(DatesOf11thMember),

	KEEPFILTERS(
	FILTER(
		DatesOf11thMember,
		[Date11thMember] = _CurDate
	)
)
)

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[littlemojopuppy]

Good morning @vivran22 !

Thanks for replying...I'm getting exactly the same result as before 

[vivran22]

@littlemojopuppy 

Is it possible to share the sample pbix file?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[littlemojopuppy]

Unfortunately the data model and all the data is in an Analysis Services database

[vivran22]

@littlemojopuppy 

Try this:

Playing:=
VAR _CurDate = SELECTEDVALUE('Calendar'[Date])
VAR	DatesOf11thMember =
	FILTER(
		SELECTCOLUMNS(
			RootOrganizations,
			"Date11thMember",
			[Date of 11th Active Member]
		),
		NOT(ISBLANK([Date11thMember]))
	)
RETURN

CALCULATE(
	COUNTROWS(DatesOf11thMember),

	KEEPFILTERS(
	FILTER(
		ALL(DatesOf11thMember),
		[Date11thMember] = _CurDate
	)
)
)

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[littlemojopuppy]

Hello again!  I tried it, and error...

I was also playing with the idea that maybe it would be easier to filter the table while defining the table variable, but I'm having the same problem.  Very frustrating.  You can see in the results pic that there's a column called "Selected Value"...that's simply SELECTEDVALUE(Calendar[Date}).  I wanted to test to make sure that it's properly getting the date, which it clearly is.  I really don't understand why it's counting rows where date <= the SELECTEDVALUE of date, and not equals the date 😖

[littlemojopuppy]

@Greg_Deckler @TomMartens could I convince you guys to please take a look?  I'd be happy to quick pay you cash for beers as thanks!  😊