Distinct CountX?

[grggmrtn]

I have the following DAX measure:

CurrentBorger = 
CALCULATE (
    COUNTX (
        FILTER (
            'nexus2 Fact_VisiteretTid',
            'nexus2 Fact_VisiteretTid'[Start] <= MAX ( Dato[Dato] )
                && (
                    ISBLANK ( 'nexus2 Fact_VisiteretTid'[Stop] )
                        || 'nexus2 Fact_VisiteretTid'[Stop] >= MAX ( Dato[Dato] )
                )
        ),
         ( 'nexus2 Fact_VisiteretTid'[CPRnrKort] )
    ),
    CROSSFILTER ( 'nexus2 Fact_VisiteretTid'[Start], Dato[Dato], NONE )
)

and it works great. Problem is, it gives me the total number of [CPRnrKort] and I need to count only the number of DISTINCT values.

Is there a way to do that, while maintaining the functionality of this measure?

[grggmrtn · Accepted solution]

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

[PaulDBrown]

@grggmrtn 

you might try wrapping the column in VALUES.

[grggmrtn]

@PaulDBrownwhat do you mean exactly?

[PaulDBrown]

@grggmrtn 

What I meant was:

CurrentBorger = 
CALCULATE (
    COUNTX (
        FILTER (
            'nexus2 Fact_VisiteretTid',
            'nexus2 Fact_VisiteretTid'[Start] <= MAX ( Dato[Dato] )
                && (
                    ISBLANK ( 'nexus2 Fact_VisiteretTid'[Stop] )
                        || 'nexus2 Fact_VisiteretTid'[Stop] >= MAX ( Dato[Dato] )
                )
        ),
         VALUES( 'nexus2 Fact_VisiteretTid'[CPRnrKort] )
    ),
    CROSSFILTER ( 'nexus2 Fact_VisiteretTid'[Start], Dato[Dato], NONE )
)

Actually, see if this works:

COUNTROWS(
    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] )
                )
        ), 
    CROSSFILTER ( 'nexus2 Fact_VisiteretTid'[Start], Dato[Dato], NONE )
)

[grggmrtn]

Eek - not quite - that took me from 1300+ to 4. I should be at about... 800-ish?

[PaulDBrown]

@grggmrtn 

Both measures returned 4?

[grggmrtn]

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)

[PaulDBrown]

@grggmrtn 

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?

[grggmrtn]

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.

[PaulDBrown]

@grggmrtn 

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)

[grggmrtn]

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

[PaulDBrown]

@grggmrtn 

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] )
                )
        )
)

[grggmrtn]

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 🙂

[PaulDBrown]

@grggmrtn 

Sounds like we are getting somewhere. Happy to help.

[grggmrtn]

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?