DAX query for grouping counts of claims by id and still impacted by date filter

[oliharford]

Hi,

I have been really struggling with a concept to build a report which matches the requirements:

Number of installers with 0 claims

Number of installers with 1 claim

Number of Installer with 2 or more claims

This also needs to be affected by a time slicer.

The data I have is:

Users

UserId
1
2
3
4
5
6
7

Claims

Id	Date	UserId
1	01/01/2022	2
2	27/01/2022	1
3	02/02/2022	2
4	20/03/2022	7

In the report one table we are trying to show is:

Count of userid	Claim bracket
4	0
2	1
1	2+

Really hoping someone can help 🙂

Regards,

Oli

[JirkaZ · Accepted solution]

The simplest way would be to create 3 different measures for that with the following logic:

SUMX(Users, IF(COUNTROWS(RELATEDTABLE(Claims)) = 0, 1, 0)
This will sum up all the users that have no claims during the slicer-selected time period. 

[vojtechsima · Accepted solution]

Hi, @oliharford, @JirkaZ 
I believe I figured it out. The solution requires only one measure and Brackets Dimension Table:

Brackets = 

var table___ = SUMMARIZE(Claims, Users[UserId ], "CountOfRequest", COUNTA(Claims[UserId]))
var _Add_ = ADDCOLUMNS(table___, "UserIDUnique", Users[UserId ], "CountOfRequestPerUser", [CountOfRequest], "Brackets_", SWITCH(TRUE(), [CountOfRequest] >= 2, "2+", [CountOfRequest] = 1, "1", "0"))

var currentBrackets = MAX(ClaimBrackets[Brackets])

var AllUsers = COUNTROWS(VALUES(Users[UserId ]))
var UsersWith2Plus = COUNTROWS(FILTER(_Add_, [CountOfRequest]>=2))
var UsersWith1 = COUNTROWS(FILTER(_Add_, [CountOfRequest]=1))
var UserWith0 = AllUsers - (UsersWith2Plus + UsersWith1)



var returnCount = SWITCH(TRUE(), 
currentBrackets = "2+", UsersWith2Plus,
currentBrackets = "1", UsersWith1,
UserWith0)

return returnCount

[JirkaZ · Accepted solution]

The simplest way would be to create 3 different measures for that with the following logic:

SUMX(Users, IF(COUNTROWS(RELATEDTABLE(Claims)) = 0, 1, 0)
This will sum up all the users that have no claims during the slicer-selected time period. 

[oliharford]

Amazing thank you for this, such a simple solution  for something i have really struggled with.