Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
for example A(5) +B(4)+C(1) = 10
Not the full sum which is 87.
and there will be same values infront for name so addition of distinct count will not work.
thanks
Solved! Go to Solution.
@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
sorry, for your second task try
Measure = SUMX(
SUMMARIZE('Table', 'Table'[Name], "DistinctDates", CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table','Table'[Name]))),
[DistinctDates]
)
Hi @Anonymous
try
Measure = CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table', 'Table'[Name]) )
hello
this is not working.
what I want is to add all the value of distinct dates count by the name.
example
- distinct count of dates by name .
this is what I want and then sum all the distinctcount of date by name . (Single time)
@Anonymous
see my second post. doesn't it ok?
No, it is adding all the values. not working.
@Anonymous
i think its completely what you need.
Name | Date |
A | 01.01.2020 |
A | 01.01.2020 |
A | 02.01.2020 |
A | 03.01.2020 |
A | 03.01.2020 |
A | 04.01.2020 |
B | 05.01.2020 |
B | 06.01.2020 |
B | 06.01.2020 |
Exactly this is what I need but not working in mine.
let me check again otherwise I'll post real data.
@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]
)
I am trying this one only.
@Anonymous
maybe you have more complicated data model then in example?
INRW01954 | 15-01-2020 |
INNS02503 | 15-01-2020 |
INSG01563 | 15-01-2020 |
INJA01639 | 01-01-2020 |
INSS00267 | 01-01-2020 |
INKM01114 | 01-01-2020 |
INAR01371 | 01-01-2020 |
INMS00148 | 01-01-2020 |
INNK01675 | 01-01-2020 |
INVR00406 | 01-01-2020 |
INYD00784 | 01-01-2020 |
INFK00954 | 01-01-2020 |
INJS00801 | 01-01-2020 |
INGM01331 | 01-01-2020 |
INAB01141 | 01-01-2020 |
INAI01710 | 01-01-2020 |
INAJ01027 | 01-01-2020 |
INMK02479 | 01-01-2020 |
INBR00802 | 01-01-2020 |
INDK00496 | 01-01-2020 |
INBT01170 | 01-01-2020 |
INGS00982 | 01-01-2020 |
INSS01931 | 01-01-2020 |
INAK00879 | 01-01-2020 |
INSK01511 | 01-01-2020 |
INAH01897 | 01-01-2020 |
INSS01592 | 01-01-2020 |
INSS00826 | 01-01-2020 |
INAS02633 | 01-01-2020 |
INVC00436 | 01-01-2020 |
INNA00674 | 01-01-2020 |
INRP00689 | 01-01-2020 |
INVP00804 | 01-01-2020 |
INRB02400 | 15-01-2020 |
INDP00831 | 01-01-2020 |
INPS01950 | 01-01-2020 |
INCB01808 | 15-01-2020 |
INVP00804 | 15-01-2020 |
INDU01645 | 01-01-2020 |
INTS01338 | 01-01-2020 |
INSS01107 | 01-01-2020 |
INPN02033 | 01-01-2020 |
INMK01836 | 01-01-2020 |
INAK02547 | 01-01-2020 |
INAK00503 | 01-01-2020 |
INAR00520 | 01-01-2020 |
INAB00693 | 01-01-2020 |
INAD01916 | 01-01-2020 |
INSN01775 | 01-01-2020 |
INSY02563 | 01-01-2020 |
INAP02048 | 15-01-2020 |
INDB02548 | 15-01-2020 |
INAS01974 | 01-01-2020 |
INDK01582 | 01-01-2020 |
INNV02390 | 01-01-2020 |
INKP01798 | 01-01-2020 |
INMP01425 | 15-01-2020 |
INKP01920 | 15-01-2020 |
INKK02020 | 15-01-2020 |
INUC02578 | 01-01-2020 |
INAY01570 | 01-01-2020 |
INNV02390 | 15-01-2020 |
INME02543 | 15-01-2020 |
INRS01496 | 15-01-2020 |
INMK01628 | 15-01-2020 |
INSN01849 | 15-01-2020 |
INAY01570 | 15-01-2020 |
INSB01739 | 01-01-2020 |
INSS01809 | 15-01-2020 |
INAG01856 | 15-01-2020 |
INPG01484 | 15-01-2020 |
INNS01796 | 15-01-2020 |
INNG01782 | 15-01-2020 |
INAK01829 | 15-01-2020 |
INSK02600 | 15-01-2020 |
INSY02563 | 15-01-2020 |
INMS00510 | 15-01-2020 |
INRP01681 | 15-01-2020 |
INSM01842 | 15-01-2020 |
INVK01813 | 15-01-2020 |
INRG01302 | 01-01-2020 |
INRG02500 | 15-01-2020 |
INRG01302 | 15-01-2020 |
INUC02578 | 15-01-2020 |
INKB02535 | 15-01-2020 |
INVN02413 | 15-01-2020 |
INSK01988 | 15-01-2020 |
INCM01749 | 15-01-2020 |
INKK02387 | 15-01-2020 |
INSC02403 | 15-01-2020 |
INKK02387 | 01-01-2020 |
INAS01974 | 15-01-2020 |
INPK01497 | 15-01-2020 |
INKP01798 | 15-01-2020 |
INDK01582 | 15-01-2020 |
INRK02539 | 01-01-2020 |
INSY02540 | 15-01-2020 |
INDK02469 | 15-01-2020 |
INRK02539 | 15-01-2020 |
INSB01739 | 15-01-2020 |
INKG02056 | 15-01-2020 |
INBM01342 | 15-01-2020 |
INNG01782 | 01-01-2020 |
INKD02498 | 01-01-2020 |
INDB02548 | 01-01-2020 |
INKD02498 | 15-01-2020 |
INAM01812 | 15-01-2020 |
INVK01813 | 01-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.
@Anonymous
it works the same even with additional columns
Could you display where is result incorrect?
This is power bi result I am getting.
result should be like yellow column and the answer should be 22
but it is giving 335.
thanks
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |