cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Highlighted
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
Highlighted
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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,459)