Forum Discussion

vanik85's avatar
vanik85
Helper I
2 years ago
Solved

Duplicate Mobile Numbers entires as a table n DAX Query View

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 punche...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
lbendlin
Super User
2 years ago

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.