Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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!
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!
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.
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |