Hi, I've searched a lot of other posts but I can't seem to solve my problem.
I have 2 main tables - Mobile & Web that record users of our apps.
There is a 3rd table called AuthActivations that records the date when each user was "activated" in our system.
I also have a DateDim (calendar) table, with a WeekNumber field so the above 3 tables can be grouped by weekly usage.
The AuthActivations need to have a cumulative value with the number of new users added on each week.
I've created a seperate table - WeeklyActivations - that lists all the weeks from my calendar table & the weekly count & cumulative sum of values from AuthActivations.
I've added 2 more columns to this table to get the weekly distinct users from the mobile & web tables.
These last 2 columns work fine when I get the users when grouped by week.
But I also want to be able to see users from a specific company - so need to be able to filter the "Mobile" & "Web" by the selected filter (a single selection of BusinessId) & just get a distinctCount each week.
BUT when I try to add in the filter for the selectedBusinessId, it returns me a whole column of 0s.
If I substitute a hard-coded value in for the SelectedValue (which is in a measure & used as a variable, "__selectedBusinessId"), it works fine.
Which leads me to believe there's something going on when I'm using a SelectedValue as a filter!
MobileCount_Weekly =
var __selectedBusinessId = IF('All Measures'[Selected BusinessId] > 0, 'All Measures'[Selected BusinessId])
var __joinColumn = 'Weekly Activations'[WeekNumber]
var __mobileCount_Weekly = CALCULATE(
DISTINCTCOUNT(Users_Mobile[MobileUserId]),
FILTER(Users_Mobile,
'Users_Mobile'[WeekNumber] = __joinColumn
&& 'Auth0Users_Mobile'[Business_Id] = __selectedBusinessId
)
)
return IF(ISBLANK(__mobileCount_Weekly), 0, __mobileCount_Weekly)
This is the line that is broken & will work if I hard-code a value
&& 'Auth0Users_Mobile'[Business_Id] = __selectedBusinessId
I've double checked that this works by using the output of the measure in a card:
var __selectedBusinessId = IF('All Measures'[Selected BusinessId] > 0, 'All Measures'[Selected BusinessId])
Please help!
