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 

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?

       

      • grggmrtn's avatar
        grggmrtn
        Post Patron
        5 years ago

        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)