Forum Discussion

TimoKytta's avatar
TimoKytta
Regular Visitor
4 years ago
Solved

Counting distinct values within a table

Hello y'all   I'm trying to build a calculation to get the following result. I've earlier managed to to some similar with instructions found here, but for some reason I don't get this work right. ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    TimoKytta , try a new column like

     


    REFcount =
    CALCULATE (
    DISTINCTCOUNT ( DailyCapa2[WORKCENTER] ),
    FILTER ( DailyCapa2, [REFID] = earlier([REFID])))

  • smpa01's avatar
    smpa01
    4 years ago

    TimoKytta  you can write a measure like this

    Measure = CALCULATE(DISTINCTCOUNT('tbl'[Workcenter]),ALLEXCEPT('tbl','tbl'[Refid]))

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

TimoKytta  you can write a measure like this

Measure = CALCULATE(DISTINCTCOUNT('tbl'[Workcenter]),ALLEXCEPT('tbl','tbl'[Refid]))

 

 

  • TimoKytta's avatar
    TimoKytta
    Regular Visitor
    4 years ago

    Thanks to you too!

     

    I chose to use this filtering style since my table has a bit over 730 000 rows and that earlier-function had a mention about possible performance issues with big tables. 

     

    Why this your solution works is a bit bigger mystery to me still! ^_^  

    Does it do so that that the column mentioned in ALLEXCEPT-function is taken as a current rows value for each row? The function documentation says that it's:  'The column for which context filters must be preserved.' 

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      TimoKytta  ALLEXCEPT is doing the <aggreagtion> based on the partition mention in ALLEXCEPT.

      One thing to always remember with DAX - if you can create a measure don't create a column.