If, then...

[EgbertMellema]

Hi all,

I hope you can help me with the following:
I have a phone database with unique id's. A unique ID is either answerd or unanswerd, or both

When answerd or unanswerd is unique I have no problem in measuring it, but when the unique ID has both answered and unanswered I only want my measure to count the answerd part, not the unanswerd.

In a matrix it would look like this:

Unique ID + Answered = Answered

Unique ID + Unanswered = Unanswered
Unique ID + Answered & Unaswered= Answered

In other words, when a unique ID has unanswerd and answered don't count the unanswered

Do you know how I can make this happen in DAX? I hope I explained it correct.

Kind regards,

Egbert 

[v-jingzhang · Accepted solution]

Hi @EgbertMellema 

It would be more helpful if you could provide some sample data and expected output.

If you want to count the number of Unique IDs, you could try:

Measure = DISTINCTCOUNT ( 'table'[Unique ID] )

If you want to add a flag to each row to mark "Answered" or "Unaswered", you could add a calculated column

New column = 
var __count = CALCULATE(DISTINCTCOUNT('Table'[Status]), FILTER('Table', 'Table'[ID] = EARLIER('Table'[ID])))
return
IF(__count=1, 'Table'[Status], "Answered")

If this is not what you want, can you provide some sample data?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@EgbertMellema , create a new column like

New column =
var _1 = calculate(distinctCOUNT(Table[Answer]), filter(Table, Table[ID] = earlier(Table[ID) && Table[Answer] in {"Unanswered","Answered"}) )
var _2 = calculate(distinctCOUNT(Table[Answer]), filter(Table, Table[ID] = earlier(Table[ID) && Table[Answer] in {"Answered"}) )
var _3 = calculate(distinctCOUNT(Table[Answer]), filter(Table, Table[ID] = earlier(Table[ID) && Table[Answer] in {"Unanswered"}) )
return
if(not(isblank(_1)) || not(isblank(_1)) , "Answered", "Unanswered")