Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
slav84
Helper I
Helper I

How to mark second occurrence of the value within same month?

‎08-18-2021 09:38 PM

Hello, 
I ahve table that has 3 columns:
Date - Range from 2020 to 2021
Name - name that repeats occasionaly
Type - contains flag if the name is new or repeated
Here is the example:

NameDateType
Smith1/1/2020Repeat
Johnson1/2/2020Repeat
Williams1/3/2020Repeat
Brown1/4/2020Repeat
Jones1/5/2020Repeat
Garcia1/6/2020Repeat
Miller1/7/2020Repeat
Davis1/8/2020Repeat
Rodriguez1/9/2020Repeat
Martinez1/10/2020Repeat
Smith1/11/2020Repeat
Johnson1/12/2020Repeat
Williams1/13/2020Repeat
Brown1/14/2020Repeat
Jones1/15/2020Repeat
Garcia1/16/2020Repeat
Miller1/17/2020Repeat
Davis1/18/2020Repeat
Rodriguez1/19/2020Repeat
Martinez1/20/2020Repeat
Smith1/21/2020Repeat
Johnson1/22/2020Repeat
Williams1/23/2020Repeat
Brown1/24/2020Repeat
Jones1/25/2020Repeat
Garcia1/26/2020Repeat
Miller1/27/2020Repeat
Davis1/28/2020Repeat
Rodriguez1/29/2020Repeat
Martinez1/30/2020Repeat
Smith1/31/2020Repeat
Johnson2/1/2020Repeat
Williams2/2/2020Repeat
Brown2/3/2020Repeat
Jones2/4/2020Repeat
Garcia2/5/2020Repeat
Miller2/6/2020Repeat
Davis2/7/2020Repeat
Rodriguez2/8/2020Repeat
Martinez2/9/2020Repeat
Sandroni2/10/2020New
Smith2/11/2020Repeat
Johnson2/12/2020Repeat
Williams2/13/2020Repeat
Brown2/14/2020Repeat
Jones2/15/2020Repeat
Garcia2/16/2020Repeat
Miller2/17/2020Repeat
Davis2/18/2020Repeat
Rodriguez2/19/2020Repeat
Martinez2/20/2020Repeat
Sandroni2/21/2020Repeat
Smith2/22/2020Repeat
Johnson2/23/2020Repeat
Williams2/24/2020Repeat
Brown2/25/2020Repeat
Jones2/26/2020Repeat
Garcia2/27/2020Repeat
Miller2/28/2020Repeat
Davis2/29/2020Repeat
Rodriguez3/1/2020Repeat
Martinez3/2/2020Repeat
Smith3/3/2020Repeat

 

What i need is to add one more colum Exclude that will contain yes value that is set only if there was a new name in the month but on it second, 3rd, 4th... occurance. 

For example name Sandroni is apearing first time in February but also it apears second time in February. Next to the first occurance of that name it should be blank but next to the second occurance of that name within same month should say Yes. 
Something like this:

NameDateTypeExclude
Smith1/1/2020Repeat 
Johnson1/2/2020Repeat 
Williams1/3/2020Repeat 
Brown1/4/2020Repeat 
Jones1/5/2020Repeat 
Garcia1/6/2020Repeat 
Miller1/7/2020Repeat 
Davis1/8/2020Repeat 
Rodriguez1/9/2020Repeat 
Martinez1/10/2020Repeat 
Smith1/11/2020Repeat 
Johnson1/12/2020Repeat 
Williams1/13/2020Repeat 
Brown1/14/2020Repeat 
Jones1/15/2020Repeat 
Garcia1/16/2020Repeat 
Miller1/17/2020Repeat 
Davis1/18/2020Repeat 
Rodriguez1/19/2020Repeat 
Martinez1/20/2020Repeat 
Smith1/21/2020Repeat 
Johnson1/22/2020Repeat 
Williams1/23/2020Repeat 
Brown1/24/2020Repeat 
Jones1/25/2020Repeat 
Garcia1/26/2020Repeat 
Miller1/27/2020Repeat 
Davis1/28/2020Repeat 
Rodriguez1/29/2020Repeat 
Martinez1/30/2020Repeat 
Smith1/31/2020Repeat 
Johnson2/1/2020Repeat 
Williams2/2/2020Repeat 
Brown2/3/2020Repeat 
Jones2/4/2020Repeat 
Garcia2/5/2020Repeat 
Miller2/6/2020Repeat 
Davis2/7/2020Repeat 
Rodriguez2/8/2020Repeat 
Martinez2/9/2020Repeat 
Sandroni2/10/2020NewNo
Smith2/11/2020Repeat 
Johnson2/12/2020Repeat 
Williams2/13/2020Repeat 
Brown2/14/2020Repeat 
Jones2/15/2020Repeat 
Garcia2/16/2020Repeat 
Miller2/17/2020Repeat 
Davis2/18/2020Repeat 
Rodriguez2/19/2020Repeat 
Martinez2/20/2020Repeat 
Sandroni2/21/2020RepeatYes
Smith2/22/2020Repeat 
Johnson2/23/2020Repeat 
Williams2/24/2020Repeat 
Brown2/25/2020Repeat 
Jones2/26/2020Repeat 
Garcia2/27/2020Repeat 
Miller2/28/2020Repeat 
Davis2/29/2020Repeat 
Rodriguez3/1/2020Repeat 
Martinez3/2/2020Repeat 
Smith3/3/2020Repeat 




How would i do this?

Labels:
Message 1 of 3
273 Views
0
1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

‎08-22-2021 11:54 PM

Hi @slav84 ,

 

According to your description, I'd suggest you create a Rank variable and then based on it to set Blank and Yes or something else:

Exclude =
VAR _rankbymonth =
    RANKX (
        FILTER (
            'Table',
            [Name] = EARLIER ( 'Table'[Name] )
                && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] )
                && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
        ),
        [Date],
        ,
        ASC,
        DENSE
    )
VAR _rankbyall =
    RANKX (
        FILTER ( 'Table', [Name] = EARLIER ( 'Table'[Name] ) ),
        [Date],
        ,
        ASC,
        DENSE
    )
RETURN
    IF (
        YEAR ( [Date] ) = MINX ( ALL ( 'Table' ), [Date].[Year] )
            && MONTH ( [Date] ) = MINX ( ALL ( 'Table' ), [Date].[MonthNo] ),
        BLANK (),
        IF (
            _rankbyall = 1
                && _rankbyall = _rankbymonth,
            "No",
            IF ( _rankbymonth = 2 && _rankbyall = _rankbymonth, "Yes" )
        )
    )

Here is the final output:

How to mark second occurrence of the value within same month.PNG

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

How to mark second occurrence of the value within same month.pbix
Message 3 of 3
171 Views
0
2 REPLIES 2
v-eqin-msft
Community Support
Community Support

‎08-22-2021 11:54 PM

Hi @slav84 ,

 

According to your description, I'd suggest you create a Rank variable and then based on it to set Blank and Yes or something else:

Exclude =
VAR _rankbymonth =
    RANKX (
        FILTER (
            'Table',
            [Name] = EARLIER ( 'Table'[Name] )
                && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] )
                && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
        ),
        [Date],
        ,
        ASC,
        DENSE
    )
VAR _rankbyall =
    RANKX (
        FILTER ( 'Table', [Name] = EARLIER ( 'Table'[Name] ) ),
        [Date],
        ,
        ASC,
        DENSE
    )
RETURN
    IF (
        YEAR ( [Date] ) = MINX ( ALL ( 'Table' ), [Date].[Year] )
            && MONTH ( [Date] ) = MINX ( ALL ( 'Table' ), [Date].[MonthNo] ),
        BLANK (),
        IF (
            _rankbyall = 1
                && _rankbyall = _rankbymonth,
            "No",
            IF ( _rankbymonth = 2 && _rankbyall = _rankbymonth, "Yes" )
        )
    )

Here is the final output:

How to mark second occurrence of the value within same month.PNG

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to mark second occurrence of the value within same month.pbix
Message 3 of 3
172 Views
0
amitchandak
Super User
Super User

‎08-18-2021 10:28 PM

@slav84 , Create a new column like

 

new column=
var _month = eomonth([date])
var _min = minx(filter(table, [Name] =earlier([Name]) && eomonth([date]) = _month), [Date])
return
if( [Date] =_min, "No" , "Yes")



Message 2 of 3
232 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

View All