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

grggmrtn's avatar
grggmrtn
Post Patron
5 years ago

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

grggmrtn 

Sounds like we are getting somewhere. Happy to help.

  • grggmrtn's avatar
    grggmrtn
    Post Patron
    5 years ago

    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?