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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors