cancel
Showing results 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

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?

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)

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.

Highlighted
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

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)