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
Whoops - no it was the second. The first killed my visual
Error was something along the lines of "Calculation error in the measure. There were many values, where there was only expected one" (sorry for the translation)
I'll hazard another guess (It's hard to pinpoint an adequate solution without sample data and the structure of the model). Let's see if taking the table calculation to a VAR makes a difference:
Measure = VAR _Table = CALCULATETABLE( VALUES( 'nexus2 Fact_VisiteretTid'[CPRnrKort] ), FILTER ( 'nexus2 Fact_VisiteretTid', 'nexus2 Fact_VisiteretTid'[Start] <= MAX ( Dato[Dato] ) && ( ISBLANK ( 'nexus2 Fact_VisiteretTid'[Stop] ) || 'nexus2 Fact_VisiteretTid'[Stop] >= MAX ( Dato[Dato] ) ) ) RETURN CALCULATE( COUNTROWS( _Table), CROSSFILTER ( 'nexus2 Fact_VisiteretTid'[Start], Dato[Dato], NONE ) )Not sure if that will make a difference, but you can check the steps by using the return function firstly to see what the VAR returns (using COUNTROWS) and see if it makes sense, and then check the impact of the second filter expression in the final CALCULATE function.
Make sense?
That's not working either - but we're up to 40, so it's 10x better hehe.
I'm not sure what's happening, it's as if my data is just acting strange.
It's a pretty basic flat table, with CPR being the personID, and Start and Stop are of course the start and stop dates.. There's 7 other columns that are just details about the transaction the person had, and I'll be filtering with them eventually - but right now I just need to get this working.I take it the 'Dato' table is a date table, right? How is the model set up in terms of relationships? What is the purpose of the CROSSFILTER?
PS, also, what is structure of the visual you are using this measure? I take it there are dates as a filter context. (You might need an FILTER ( ALL('nexus2 Fact_VisiteretTid') ...to remove the date filter context and let the filter expressions kick in)