topic Re: Want to Count Items that Appears More than Once in DAX Commands and Tips

Want to Count Items that Appears More than Once

Dyke211 — Wed, 15 May 2024 10:33:47 GMT

I have a problem. i have this column columns in my data set and i want to be able to count each object class that appears more than once within a month..I also have a different calender table and made a relationship with this "Obj_reported".. I have tried this and sis not work

"CountObjectsOverOncePerMonth =
VAR Event_Count =
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Obj_Classs], YEAR(Data[Obj_Reported]), MONTH(Data[Obj_Reported])),
"Count", CALCULATE ( COUNTROWS ( Data), ALLEXCEPT ( Data, Data[Obj_Classs], YEAR(Data[Obj_Reported]), MONTH(Data[Obj_Reported])) )
)
VAR Objects_Beyond_1 = FILTER ( Event_Count, [Count] > 1 )
RETURN
COUNTROWS ( Objects_Beyond_1 )
"

Obj_Classs	Obj_Reported
A	20/02/2021
A	21/02/2021
B	20/04/2021
C	27/06/2021
G	22/08/2021
B	27/07/2021
B	27/12/2021
G	30/08/2021
D	21/02/2023
E	21/02/2021
A	21/11/2021
G	07/08/2021
G	08/10/2022

Re: Want to Count Items that Appears More than Once

Jihwan_Kim — Wed, 15 May 2024 17:28:46 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

More than once within the same month: = VAR _t = SUMMARIZE ( Data, Obj_Classs[Obj_Classs], 'Calendar'[Year-Month sort], 'Calendar'[Date] ) VAR _conditiontable = FILTER ( ADDCOLUMNS ( _t, "@appearancecount", COUNTROWS ( FILTER ( _t, Obj_Classs[Obj_Classs] = EARLIER ( Obj_Classs[Obj_Classs] ) && 'Calendar'[Year-Month sort] = EARLIER ( 'Calendar'[Year-Month sort] ) ) ) ), [@appearancecount] > 1 ) RETURN COUNTROWS ( SUMMARIZE ( _conditiontable, Obj_Classs[Obj_Classs] ) )

Re: Want to Count Items that Appears More than Once

Anonymous — Thu, 16 May 2024 06:09:12 GMT

Hi Dyke211

Thanks for the reply from Jihwan_Kim , please allow me to provide another insight:

Create a measure:

MEASURE = VAR _Event_Count = SUMMARIZE ( ADDCOLUMNS ( 'Data', "_Year_Month", FORMAT ( 'Data'[Obj_Reported], "YYYY-MM" ), "_Count", CALCULATE ( COUNTROWS ( 'Data' ), FILTER ( ALL ( Data ), 'Data'[Obj_Classs] = EARLIER ( Data[Obj_Classs] ) && YEAR ( 'Data'[Obj_Reported] ) = YEAR ( EARLIER ( 'Data'[Obj_Reported] ) ) && MONTH ( 'Data'[Obj_Reported] ) = MONTH ( EARLIER ( Data[Obj_Reported] ) ) ) ) ), [Obj_Classs], [_Year_Month], [_Count] ) RETURN COUNTROWS ( FILTER ( _Event_Count, [_Count] > 1 ) )

The result:

Or you can create a table:

Table = FILTER ( SUMMARIZE ( ADDCOLUMNS ( 'Data', "_Year_Month", FORMAT ( 'Data'[Obj_Reported], "YYYY-MM" ), "_Count", CALCULATE ( COUNTROWS ( 'Data' ), FILTER ( ALL ( Data ), 'Data'[Obj_Classs] = EARLIER ( Data[Obj_Classs] ) && YEAR ( 'Data'[Obj_Reported] ) = YEAR ( EARLIER ( 'Data'[Obj_Reported] ) ) && MONTH ( 'Data'[Obj_Reported] ) = MONTH ( EARLIER ( Data[Obj_Reported] ) ) ) ) ), [Obj_Classs], [_Year_Month], [_Count] ), [_Count] > 1 )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.