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

az38's avatar
az38
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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