cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

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

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

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

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]) )                     ) `
Established Member

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

Hi Angel,

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

( screensnot below)

5 REPLIES 5
Super User

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

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]) )                     ) `
Frequent Visitor

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

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
Established Member

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

Hi Angel,

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

( screensnot below)

Highlighted
Super User

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

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.

Regular Visitor

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

@areymejias  regarding repeated, you can try:

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

## Helpful resources

Announcements

#### New Ranks and Rank Icons in 2020

Read the announcement for more information!

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)