Want to Count Items that Appears More than Once

[Dyke211]

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

[Jihwan_Kim · Accepted solution]

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] ) )

[v-zhengdxu-msft · Accepted solution]

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.

[Jihwan_Kim · Accepted solution]

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] ) )