Forum Discussion
Distinct CountX?
Ok I actually got it figured out, and it was a LOT easier than I thought it should have been 😄
First, I got rid of the relations between the tables.
Then it was just a matter of creating the following measure:
Measure = VAR StartDato = MIN ( Dato[Dato] ) VAR SlutDato = MAX ( Dato[Dato] ) RETURN CALCULATE ( DISTINCTCOUNT('nexus2 Fact_VisiteretTid'[CPRnrKort]), FILTER ( 'nexus2 Fact_VisiteretTid', ( 'nexus2 Fact_VisiteretTid'[Start] <= SlutDato && 'nexus2 Fact_VisiteretTid'[Stop] >= StartDato ) ) )Seriously, it solved all my problems, and the result validates. Go figure XD
You guessed correct. Dato[Dato] is related to [Start] and [Stop] respectively, many-to-one, one-way. The relation to [Start] is active, the relation to [Stop] is not.
The purpose of CROSSFILTER is... because I found the code somewhere else and it looked pretty? And also, it got me closer to anything else I've tried...
I've also tried a simple
Currentborgere2 =
VAR StartDato =
MIN ( Dato[Dato] )
VAR SlutDato =
MAX ( Dato[Dato] )
RETURN
CALCULATE(
DISTINCTCOUNT('nexus2 Fact_VisiteretTid'[CPRnrKort]),
FILTER('nexus2 Fact_VisiteretTid', ('nexus2 Fact_VisiteretTid'[Start] >= StartDato && 'nexus2 Fact_VisiteretTid'[Stop] <= SlutDato )
)
)but that's not doing anything interesting either. I'm REALLY confused about why my data is behaving this way lol
Really need to see how you are setting up the visual (date from the date table? etc...)
If you have an active relationship between the date table and the [start] date you don't need the CROSSFILTER expression (assuming you have the date in the visual itself).
You might try:
Measure = COUNTROWS(
CALCULATETABLE(
VALUES( 'nexus2 Fact_VisiteretTid'[CPRnrKort] ),
FILTER (
ALL('nexus2 Fact_VisiteretTid'),
'nexus2 Fact_VisiteretTid'[Start] <= MAX ( Dato[Dato] )
&& (
ISBLANK ( 'nexus2 Fact_VisiteretTid'[Stop] )
|| 'nexus2 Fact_VisiteretTid'[Stop] >= MAX ( Dato[Dato] )
)
)
)Standard grouped bar chart, axis is Date[Dato] (date table), so 1 bar for each day.
and... your latest measure works, but there's something wrong with it (filtering by the other columns in the table gives the same value across the board) so I'm going to have to validate before I can give you any details.But end of workday here - hope you're around tomorrow so I can bother you some more 😉
Thanks for your patience 🙂
Sounds like we are getting somewhere. Happy to help.
So I think I should start over here, and explain the data and goal a bit more, because I'm no longer 100% sure that the measure I started out with was the right one to begin with(!):
I have the following columns in 'nexus2 Fact_VisiteretTid':
CPRKort - (id)int
Birthday (date)
ServiceEntity (string)
ServicePayer (string)
ServiceName (string)
TransactionId (int)
Category (string)
Start (date)
Stop (date)The detached date table (Dato) is just
CALENDAR(MIN('nexus2 Fact_VisiteretTid'[Start]), MAX('nexus2 Fact_VisiteretTid'[Stop]))with a bunch of columns added for year, year-month, weeknumber, quarter etc.
They are related to this table twice. One active relation from Dato[dato] to [start], and the other, inactive, from Dato[dato] to [stop].
The goal with the measure mentioned is, when slicing by Dato[dato], or using Dato[dato] or it's derivitives as an axis in a chart or table, to list the number of unique CPRKort where Dato[dato] hits somewhere between [start] and [stop].
This means, that if I have a chart with an axis by year, and a CPRKort that has [start] as 1.06.2019 and stop as 14.08.2020, then that CPRKort should be counted BOTH in 2019 and 2020.
If filtering by month, then the CPRKort should be counted also in august 2020, because they were "active" for part of that month.
I hope I'm I making more sense?