a way for distinctcount return a different column

[thorn]

Hi, Im not sure if there is any possibility to make a distinct count returning a different column of the distinct value, its a bit hard to explain, so example:

Values    Number       date

A               3               date1

A               3               date2

B               5               date1

B               5               date2

B               5               date3

What I need is a Sum of the "Number" column with a variable date selection but only 1 Number for each Distinct Value. So, if a user selects Date 1 the result is 8, if Date 3 the result is 5, but the part which is hard if he selects Dates 1 to 3, then the result needs to be 8 too. So one cannot do it with sums which would give a nonsensical 21. One likely has to incorporate the distinct part of it, but Im not sure how I can write the if I do a distinctcount than instead of giving a 1 for each unique value the function would pull the number associated with that unique value from the Number column.

[qqqqqwwwweeerrr]

Hi @thorn 

you achieve by this by creating these two measure 

DistinctSumofnumber = SUMX(DISTINCT('Sheet5'[Number]), 'Sheet5'[Number])

then based on selection of date in filter:

SwitchMeasure =

VAR SelectedCount = COUNTROWS(VALUES('Sheet5'[date]))

RETURN

    IF(

        SelectedCount = 1,

        SUM(Sheet5[Number]),

        [DistinctSum]

    )

so by using these two you can get the disred ouptut:

Above if all the selected then i will sum distinct value now if just select date1

it will give 8 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards

[thorn]

Hi, thank you for the help. I tried it and in case of single-date selections the SwitchMeasure gives fine numbers, but the multidate does not work instead it gives me a static number no matter what multidate selection I make.

Maybe important, in the post I did not specify that the dataset is a few million rows and neither the 'Value' nor 'Number' columns are unique. 'Number's are repetitive for many different 'Value's, and occasionally a same 'Value' might repeat (not on same Date though) with same or different 'Number'.

For single-date selections I just make sums of the 'Number's which shows correctly, i.e. the same as your SwitchMeasure, for the multidate I guess I would somehow need to leverage the relative distinctness of the 'Value' so that whatever 'Number' is next to it in the selected dates gets counted only once instead of several times (as the 'Number' represents number of unique people that I do not have an unique identifier for that I could DistinctCount, while 'Value' represents the account which booked, which in a small timeframe i.e. week, is unique).

Apologies for not providing more details right away.