Forum Discussion

thorn's avatar
thorn
Frequent Visitor
2 years ago

a way for distinctcount return a different column

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     ...
Need Help
qqqqqwwwweeerrr's avatar
qqqqqwwwweeerrr
Solution Sage
2 years ago

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's avatar
thorn
Frequent Visitor
2 years ago

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.