Count different values in a row and add it to a calculated field

[areymejias]

Hello everyone, thanks in advance for helping. I am kinda new in Power Bi and DAS.

I do have a table with 2 fields, Patient (Identifier of my patients) and Date (Exact day when I assist them). I do want to obtain a new 2 columns.

The first one (Unique) should let me know if I have seen the patient before (yes) or just 1 time (no).

The second one (Repeated) should let me know how many times I have seen the patient.

I post here an example of 4 patients and who the new calculated fields (columns Unique and repeated) should look like.

Patient	Date	Unique	Repeated
AR	01/01/2018	NO	1
AR	03/01/2018	NO	2
AR	01/03/2017	NO	0
UN1	04/08/2019	YES	0
UN2	07/02/2015	YES	0
BO	01/01/2015	NO	1
BO	02/02/2016	NO	2
BO	08/07/2019	NO	3
BO	01/02/2000	NO	0

Any ideas,

Thanks a lot!

[Cmcmahan · Accepted solution]

Sure.  Unique should be a pretty easy DAX measure:

Unique = IF( CALCULATE( COUNT(Table1[Patient]), ALL(Table1) ) > 1, 
        "NO",  
        "YES") 

Repeated is slightly harder, but still easy DAX:

Repeated = CALCULATE( COUNT(Table1[Patient]), 
                      FILTER(ALLEXCEPT(Table1, Table1[Patient]), Table1[Date] < SELECTEDVALUE(Table1[Date]) ) 
                    ) 

[areymejias]

Hello @Cmcmahan , Thanks a lot for your reply!!

But it does not work. If you try it with the table above. It returns an error message: "The syntax for "NO" is an error (DAX(

IF( CALCULATE( COUNT(Table1[Patient]); ALL(Table1) ) > 1,"NO"; "YES")

Any ideas?

Thanks again for your time!

Angel

[tejaswidmello · Accepted solution]

Hi Angel,

Could it be because you are giving semicolon and not comma after NO?  

( screensnot below)

[Cmcmahan]

Yup. This is 100% due to regional settings.  You'll have that issue pretty commonly on the board. There's a good mix of us that use commas instead of semicolons and vice versa.  Double check any queries you get here for basic structure.

[mochabits]

@areymejias  regarding repeated, you can try:

repeated = RANKX(FILTER(History, History[Patient] = EARLIER(History[Patient])), History[drDate], ,ASC) - 1