Forum Discussion
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 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!
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! 😀
8 Replies
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! 😀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 TwitterIs it possible to share the sample pbix file?
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
