Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
areymejias
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
Cmcmahan
Resident Rockstar
Resident Rockstar

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

Anonymous
Not applicable

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
mochabits
Helper II
Helper II

@areymejias  regarding repeated, you can try:

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

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]) )
)

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

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.

Anonymous
Not applicable

Hi Angel,

 

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

( screensnot below)

 

Capture 8.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.