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
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 🙂