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] )
                && (
                    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?

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

15 Replies

      • 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

    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