Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
5 years ago
Solved

Problem With Counting Records by Date

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 invoici...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    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's avatar
vivran22
Community Champion
5 years ago

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