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
CWP123
New Member

Grouping and Counting by multiple columns in DAX for if/then column

Hi all, like the title says I'm pretty stuck on a requirement levied for a customers employee disease tracking project.

 

Essentially, I would need to return a True\False value given the condition of:

>>if count_of_records >=5 then "true" else "false"

 

I'm using a sharepoint hosted list and the table is mirrored by the small table at the end of this post.

My plan was too..

1) First group all records by Store_ID

2) Once records are grouped by Store_ID, then group by Date_of_Infection

3) Count the records by Date_of_infection

4) Return true if count >= 5 else return false.

 

We can not use SQL, unfortunately, for this project an so I'm left trying to figure out how to translate those steps into a functioning series of dax measures or PowerM.

 

The end goal is to be able to identify "clusters" as defined by my customer where 5+ reported daily infections occur at a single site. For example, if 10 employees were sick this would generate 5 reporting_date & date_of_infection values. If 5+ values occured on the same day at the same store_id this would be a "cluster". 

 

Any help is greatly appreciated an thank you very much in advance!

 

**In this table the rows with Store_id == 1 would return true, all others would return False.

 

Store_iddate_of_infection
13-1-2021
13-1-2021
13-1-2021
13-1-2021
13-1-2021
23-2-2021
23-2-2021

 

1 REPLY 1
PaulOlding
Solution Sage
Solution Sage

Does this calculated column give you what you need?

 

Multi =
VAR RowCount = CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table, Table[Store_id], Table[date_of_infection]))
RETURN
IF(RowCount>=5, "True", "False")

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.

Top Solution Authors