Most repeated text value for time range

[Zain26]

Hi guys, I am new to Power BI. I want to calculate the most occured text value from a column. I have three columns one is names of users, other is date/time column and the last one is time ranges. 
Names Date/Time         Time Range  
Alan     5/7/2017 13:30        12pm-3pm    
Alan     3/7/2017 22:45        9pm-12am

Alan     1/7/2017 14:30        12pm-3pm
Alan     1/6/2017 13:30        12pm-3pm
Peter    23/7/2017 19:35         6pm-9pm

Peter    24/7/2017 13:30        12pm-3pm
Peter    9/7/2017 14:30        12pm-3pm
Peter    9/6/2017 14:30        12pm-3pm
Smith   19/7/2017 4:30         3am-6am

Smith   5/7/2017 10:30         9am-12pm

Smith   5/7/2017 3:30          3am-6am

Smith   1/5/2016 3:30          3am-6am 
What I want to achieve is Most Ocurring time range for each name filtered on the current month. 
The result should be 12pm-3pm for Alan, 12pm-3pm for Peter, 3am-6am for Smith. I want to put the range in a matrix visualization. 
I can achieve the current month part since I have already used it in other measures. 

[AlbertoFerrari]

You might have ties in the range detection, in such a case you should decide what to do. In the following code, if there are ties I return all of them concatenated. Anyway, by modifying the code, you should be able to obtain what you need:

FTR = 
CONCATENATEX(
    TOPN (
        1, 
        ADDCOLUMNS(
            SUMMARIZE ( Ranges, Ranges[Name], Ranges[TimeRange] ),
            "Occurrences", CALCULATE( COUNTROWS ( Ranges ) )
        ),
        [Occurrences]
    ),
    Ranges[TimeRange],
    ","
)

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[v-qiuyu-msft]

Hi @Zain26,

You can create a calculated table below: 

Table = SUMMARIZE('Table2','Table2'[Names],'Table2'[Time Range],"count",COUNT(Table2[Time Range]))

Then create a measure: 

Most Ocurring time range = LOOKUPVALUE('Table'[Time Range],'Table'[Names],MAX('Table'[Names]),'Table'[count],CALCULATE(MAX('Table'[count]),ALLEXCEPT('Table','Table'[Names])))

Best Regards,
Qiuyun Yu 

[Zain26]

I receive an error using your solution.  Here is what I actually want to acquire from the data. 

These are my columns, where CountofTimeRange  is a calculated column which shows me the count of each time range occuring for a user in the specified dates. 

USER  |  TimeRange | CountofTimeRange| 

BANK       9pm-12am     54 

BANK       6pm-9pm       24
BANK       6pm-9pm       24

BANK       3pm-6pm       54

STORE      9pm-12am     96 

STORE      12pm-3pm     34
STORE      6pm-9pm       78

STORE      3pm-6pm       10

SCHOOL    9am-12pm    5 

SCHOOL   12pm-3pm     120
SCHOOL   6pm-9pm       1

SCHOOL   3pm-6pm       2

================================== 
I want to get the value 9pm-12am against th BANK column, in a row using matrix visualization. and ignore the lesser time range. However when I attempt to do this in a measure using 
VAR peaktime= Calculate(LookupValue([Timerange],[CountofTimeRange],MAX([CountofTimeRange])),[Date].[month]>= DATE(maxyear,maxmonth,1))
receive an error which says 
A table of multiple values was supplied where a single value was expected. 

Using the Matrix visual the result should be something like this, 
                    BANK             STORE             SCHOOL

PeakTime    9pm-12am     9pm-12am       12pm-3pm