Duplicate Mobile Numbers entires as a table n DAX Query View

[vanik85]

I have been trying to display the mobile numbers which occur more than once in a month in DAX editor view  along with their enttry dates 

e.g. 

Mobile 1 puched in system 1-April

Mobile 2 punched in system 3 Apr

Mobile 1 punched in system 4-Apr

Mobile 3  punched in system 4-Apr

Mobile 3 punched in system 7-Apr

Mobile 4 punched in system 10- Apr

Mobile 5 punched in system  15- Apr

Mobile 5 punched in system.  15-Apr

I wish to display only Mobile 1 Mobile 3 and Mobile 5 in DAX Query View as these mobiles have multiple entires 

View Desired in DAX Query

Mobile 1  1-Apr.    count1

Mobile 1  4-Apr.    count2

Mobile 3.  4-Apr.   count 1

Mobile 3   7-Apr.   count 2

Mobile 5.  15-Apr. count1

Mobile 5 15-Apr   count 2

So far I have tried using below but still getting unique mobile like Mobile 2 and 4

DEFINE
    COLUMN Table[test] =
        COUNTROWS (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                ALLSELECTED ( [Mobile Number], [Date], [Yr-Mnth] ),
                ORDERBY ( [Date] ),
                PARTITIONBY ([Mobile Number], [Yr-Mnth] )
            )
        )

EVALUATE
SUMMARIZECOLUMNS (
    [Mobile Number],
    [Date],
    [Month Unique],
    [test],
    FILTER (
        Table,
        [Utm_Source(FT)] = "google-search"
            && MONTH ( [Date] ) = 3 
            && COUNTROWS (
            WINDOW (
                1,
                ABS,
                -1,
                ABS,
                ALLSELECTED ( [Mobile Number], [Date], [Yr-Mnth] ),
                ORDERBY ( [Date] ),
                PARTITIONBY ( [Mobile Number], [Yr-Mnth] )
            ))>1

    )
)
ORDER BY [Mobile Number], Date]

Thanks

[v-huijiey-msft · Accepted solution]

Hi @vanik85 ,

Thanks for the reply from lbendlin .

Because the dates in Mobile 5 are repeated, an index column needs to be added for technical convenience.

Create a calculated column to extract Day and Month:

Yr-Mnth = DAY('Table'[Date]) &"-"& 'Table'[Date].[Month]

Create a measure:

Count = 
VAR _currentYM =
    MAX ( 'Table'[Yr-Mnth] )
VAR _currentMobileNumber =
    SELECTEDVALUE ( 'Table'[Mobile Number] )
VAR _currentIndex =
    MAX ( 'Table'[Index] )
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Yr-Mnth] <= _currentYM
                && 'Table'[Mobile Number] = _currentMobileNumber
                && 'Table'[Index] <= _currentIndex
        )
    )
VAR _vtable =
    SUMMARIZE (
        FILTER (
            SELECTCOLUMNS (
                ALLSELECTED ( 'Table' ),
                'Table'[Mobile Number],
                'Table'[Yr-Mnth],
                "_NEWCOUNT",
                    COUNTX (
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            'Table'[Yr-Mnth] <= EARLIER ( 'Table'[Yr-Mnth] )
                                && 'Table'[Mobile Number] = EARLIER ( 'Table'[Mobile Number] )
                        ),
                        'Table'[Mobile Number]
                    )
            ),
            [_NEWCOUNT] > 1
        ),
        [Mobile Number],
        [_NEWCOUNT]
    )
RETURN
    IF (
        FIND (
            SELECTEDVALUE ( 'Table'[Mobile Number] ),
            CONCATENATEX ( _vtable, [Mobile Number] ),
            ,
            BLANK ()
        )
            <> BLANK (),
        _count
    )

The final page effect is shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

No need for DAX

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9Q3MjAyUYrViVYyAgsYIwQgKkwQAsbYBcwRAiYQQw0QIqYQEVOcIrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mobile = _t, Date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Mobile"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table [Date=nullable date, Index=Int64.Type]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.