cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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]) )                     ) `
Anonymous
Not applicable

Hi Angel,

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

( screensnot below)

5 REPLIES 5
Helper II

@areymejias  regarding repeated, you can try:

repeated = RANKX(FILTER(History, History[Patient] = EARLIER(History[Patient])), History[drDate], ,ASC) - 1
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]) )                     ) `
Frequent Visitor

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
Resident Rockstar

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors