Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

how to Count distinct date value by Name ?

hello,    How to calculate distinct date value by Name? want dax for it. I want this column in power bi ( countdistinct of date by Name). Plus then sum all the values of name single time.  ...
  • az38's avatar
    az38
    6 years ago

    Anonymous 

    as you use a date slicer you could try this

    Measure = 
var _minDate = CALCULATE(MIN('Table'[Date]),ALLSELECTED('Table'))
var _maxDate = CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
return
SUMX(SUMMARIZE('Table', 'Table'[Name], "DistinctDates", 
CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table','Table'[Name]), DATESBETWEEN('Table'[Date], _minDate, _maxDate))),[DistinctDates]
)

    to notificate me about new questions please mark me with @ symbol

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Exactly this is what I need but not working in mine. 

let me check again otherwise I'll post real data.

 

az38's avatar
az38
Community Champion
6 years ago

Anonymous 

pay attention, I suggested 2 measures, try this statement:

Measure = SUMX(
SUMMARIZE('Table', 'Table'[Name], "DistinctDates", CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table','Table'[Name]))),
[DistinctDates]
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I am trying this one only. 

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    maybe you have more complicated data model then in example?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    INRW0195415-01-2020
    INNS0250315-01-2020
    INSG0156315-01-2020
    INJA0163901-01-2020
    INSS0026701-01-2020
    INKM0111401-01-2020
    INAR0137101-01-2020
    INMS0014801-01-2020
    INNK0167501-01-2020
    INVR0040601-01-2020
    INYD0078401-01-2020
    INFK0095401-01-2020
    INJS0080101-01-2020
    INGM0133101-01-2020
    INAB0114101-01-2020
    INAI0171001-01-2020
    INAJ0102701-01-2020
    INMK0247901-01-2020
    INBR0080201-01-2020
    INDK0049601-01-2020
    INBT0117001-01-2020
    INGS0098201-01-2020
    INSS0193101-01-2020
    INAK0087901-01-2020
    INSK0151101-01-2020
    INAH0189701-01-2020
    INSS0159201-01-2020
    INSS0082601-01-2020
    INAS0263301-01-2020
    INVC0043601-01-2020
    INNA0067401-01-2020
    INRP0068901-01-2020
    INVP0080401-01-2020
    INRB0240015-01-2020
    INDP0083101-01-2020
    INPS0195001-01-2020
    INCB0180815-01-2020
    INVP0080415-01-2020
    INDU0164501-01-2020
    INTS0133801-01-2020
    INSS0110701-01-2020
    INPN0203301-01-2020
    INMK0183601-01-2020
    INAK0254701-01-2020
    INAK0050301-01-2020
    INAR0052001-01-2020
    INAB0069301-01-2020
    INAD0191601-01-2020
    INSN0177501-01-2020
    INSY0256301-01-2020
    INAP0204815-01-2020
    INDB0254815-01-2020
    INAS0197401-01-2020
    INDK0158201-01-2020
    INNV0239001-01-2020
    INKP0179801-01-2020
    INMP0142515-01-2020
    INKP0192015-01-2020
    INKK0202015-01-2020
    INUC0257801-01-2020
    INAY0157001-01-2020
    INNV0239015-01-2020
    INME0254315-01-2020
    INRS0149615-01-2020
    INMK0162815-01-2020
    INSN0184915-01-2020
    INAY0157015-01-2020
    INSB0173901-01-2020
    INSS0180915-01-2020
    INAG0185615-01-2020
    INPG0148415-01-2020
    INNS0179615-01-2020
    INNG0178215-01-2020
    INAK0182915-01-2020
    INSK0260015-01-2020
    INSY0256315-01-2020
    INMS0051015-01-2020
    INRP0168115-01-2020
    INSM0184215-01-2020
    INVK0181315-01-2020
    INRG0130201-01-2020
    INRG0250015-01-2020
    INRG0130215-01-2020
    INUC0257815-01-2020
    INKB0253515-01-2020
    INVN0241315-01-2020
    INSK0198815-01-2020
    INCM0174915-01-2020
    INKK0238715-01-2020
    INSC0240315-01-2020
    INKK0238701-01-2020
    INAS0197415-01-2020
    INPK0149715-01-2020
    INKP0179815-01-2020
    INDK0158215-01-2020
    INRK0253901-01-2020
    INSY0254015-01-2020
    INDK0246915-01-2020
    INRK0253915-01-2020
    INSB0173915-01-2020
    INKG0205615-01-2020
    INBM0134215-01-2020
    INNG0178201-01-2020
    INKD0249801-01-2020
    INDB0254801-01-2020
    INKD0249815-01-2020
    INAM0181215-01-2020
    INVK0181301-01-2020

    This is the data I am using, and just for your Infromation there were a lot of more columns which I have detailed because of confidential data.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    it works the same even with additional columns

    Could you display where is result incorrect?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This is power bi result I am getting. 

    Measure = SUMX(
    SUMMARIZE(Raw_BusinessWorld,Raw_BusinessWorld[Resource_Id], "DistinctDates", CALCULATE(DISTINCTCOUNT(Raw_BusinessWorld[Trans_Date]), ALLEXCEPT(Raw_BusinessWorld,Raw_BusinessWorld[Resource_Id]))),
    [DistinctDates]
    )
    using this dax
     
    and this is the excel exported result of the column 

    result should be like yellow column and the answer should be 22 

    but it is giving 335. 

    thanks

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    as you use a date slicer you could try this

    Measure = 
var _minDate = CALCULATE(MIN('Table'[Date]),ALLSELECTED('Table'))
var _maxDate = CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
return
SUMX(SUMMARIZE('Table', 'Table'[Name], "DistinctDates", 
CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table','Table'[Name]), DATESBETWEEN('Table'[Date], _minDate, _maxDate))),[DistinctDates]
)

    to notificate me about new questions please mark me with @ symbol