topic Need a measure to obtain only one value per client out of many forms in DAX Commands and Tips

Need a measure to obtain only one value per client out of many forms

NumeroENAP — Fri, 24 Jan 2020 16:11:33 GMT

Here is an exemple of my model :

NO_Evaluation	Question	Date	Conformity
1	Q1	2020-01-23	Conform
1	Q2	2020-01-23	Conform
1	Q3	2020-01-23	Conform
1	Q4	2020-01-23	Conform
2	Q1	2020-01-24	Not conform
2	Q2	2020-01-24	Not conform
2	Q3	2020-01-24	Not conform
2	Q4	2020-01-24	Not conform

And this is what I want my measure to do :

Conformity	NB
Conform	1
Not conform	1

I also have a separate table with the dates of the current fiscal years ("FiscalYear"), and I want my measure to filter the results to only show results with dates that matches the ones in my "FiscalYear" column.

Thanks!

Re: Need a measure to obtain only one value per client out of many forms

Anonymous — Fri, 24 Jan 2020 18:02:26 GMT

Try something like
Measure= calculate(distinctcount(table[id]),contains(table,table[date],fiscal[date])

If it resolves your problem mark it as a solution and give Kudos.

Thanks
Pravin

Re: Need a measure to obtain only one value per client out of many forms

NumeroENAP — Fri, 24 Jan 2020 20:56:50 GMT

Sorry Anonymous , I forgot to mention that I wanted it to return the lastest completed form of each table[ID].

Thanks!

Re: Need a measure to obtain only one value per client out of many forms

Anonymous — Sat, 25 Jan 2020 02:52:27 GMT

Do u mean latest date per id?

Add one more condition in above measure.
Filter(all(table[date],table[date]=max(table[date]))

Thanks,
Pravin

If it resolves your problem mark it as a solution and give Kudos.

Re: Need a measure to obtain only one value per client out of many forms

NumeroENAP — Tue, 28 Jan 2020 16:09:25 GMT

Well... I have 38 completed forms, but the results it gives me it :

Conform : 1
Not conform : 1

Obviously, it gives me the ID that matches the newest [date].