Forum Discussion

grggmrtn's avatar
grggmrtn
Post Patron
5 years ago
Solved

Distinct CountX?

I have the following DAX measure: CurrentBorger = CALCULATE ( COUNTX ( FILTER ( 'nexus2 Fact_VisiteretTid', 'nexus2 Fact_VisiteretTid'[Start] <= MAX ( Dato[Dato...
Need Help
  • grggmrtn's avatar
    grggmrtn
    5 years ago

    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's avatar
PaulDBrown
Community Champion
5 years ago

grggmrtn 

you might try wrapping the column in VALUES.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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's avatar
      grggmrtn
      Post Patron
      5 years ago

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

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        5 years ago

        grggmrtn 

        Both measures returned 4?