cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
areymejias Frequent Visitor
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.

 

PatientDateUniqueRepeated
AR01/01/2018NO1
AR03/01/2018NO2
AR01/03/2017NO0
UN104/08/2019YES0
UN207/02/2015YES0
BO01/01/2015NO1
BO02/02/2016NO2
BO08/07/2019NO3
BO01/02/2000NO0

Any ideas,

Thanks a lot!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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]) )
)

View solution in original post

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

 

Capture 8.PNG

View solution in original post

5 REPLIES 5
Super User
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]) )
)

View solution in original post

areymejias Frequent Visitor
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
tejaswidmello Established Member
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)

 

Capture 8.PNG

View solution in original post

Super User
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.

mochabits Regular Visitor
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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

Difinity Conference

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)