Most repeated text value for time range

Question

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.&nbsp;Names Date/Time&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time Range&nbsp;&nbsp;Alan&nbsp; &nbsp; &nbsp;5/7/2017 13:30&nbsp; &nbsp; &nbsp; &nbsp; 12pm-3pm&nbsp; &nbsp;&nbsp;Alan&nbsp; &nbsp; &nbsp;3/7/2017 22:45&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; 9pm-12amAlan&nbsp; &nbsp; &nbsp;1/7/2017 14:30&nbsp; &nbsp; &nbsp; &nbsp; 12pm-3pmAlan&nbsp; &nbsp; &nbsp;1/6/2017 13:30&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; 12pm-3pmPeter&nbsp; &nbsp; 23/7/2017 19:35&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 6pm-9pmPeter&nbsp; &nbsp; 24/7/2017 13:30&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;12pm-3pmPeter&nbsp; &nbsp; 9/7/2017 14:30&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;12pm-3pmPeter&nbsp; &nbsp;&nbsp;9/6/2017&nbsp;14:30&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;12pm-3pmSmith&nbsp; &nbsp;19/7/2017 4:30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3am-6amSmith&nbsp; &nbsp;5/7/2017 10:30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9am-12pmSmith&nbsp; &nbsp;5/7/2017 3:30&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;3am-6amSmith&nbsp; &nbsp;1/5/2016&nbsp;3:30&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;3am-6am&nbsp;What I want to achieve is Most Ocurring time range for each name filtered on the current month.&nbsp;The result should&nbsp;be 12pm-3pm for Alan, 12pm-3pm for Peter, 3am-6am for Smith. I want to put the range in a matrix visualization.&nbsp;I can achieve the current month part since I have already used it in other measures.&nbsp;&nbsp;

v-qiuyu-msft · Answer

Hi&nbsp;Zain26,
&nbsp;
You can create a calculated table below:&nbsp;
Table = SUMMARIZE('Table2','Table2'[Names],'Table2'[Time Range],"count",COUNT(Table2[Time Range]))
&nbsp;
Then create a measure:&nbsp;
Most Ocurring time range = LOOKUPVALUE('Table'[Time Range],'Table'[Names],MAX('Table'[Names]),'Table'[count],CALCULATE(MAX('Table'[count]),ALLEXCEPT('Table','Table'[Names])))
&nbsp;

Best Regards,Qiuyun Yu&nbsp;

zain26 · Answer

I receive an error using your solution.&nbsp; Here is what I actually want to acquire from the data.&nbsp;These are my columns, where&nbsp;CountofTimeRange&nbsp; is a calculated column which shows me the count of each time range occuring for a user in the specified dates.&nbsp;&nbsp;USER&nbsp; |&nbsp; TimeRange&nbsp;| CountofTimeRange|&nbsp;BANK&nbsp; &nbsp; &nbsp; &nbsp;9pm-12am&nbsp; &nbsp; &nbsp;54&nbsp;BANK&nbsp; &nbsp; &nbsp; &nbsp;6pm-9pm&nbsp; &nbsp; &nbsp; &nbsp;24BANK&nbsp; &nbsp; &nbsp; &nbsp;6pm-9pm&nbsp; &nbsp; &nbsp; &nbsp;24BANK&nbsp; &nbsp; &nbsp; &nbsp;3pm-6pm&nbsp; &nbsp; &nbsp; &nbsp;54&nbsp;STORE&nbsp; &nbsp; &nbsp; 9pm-12am&nbsp; &nbsp; &nbsp;96&nbsp;STORE&nbsp; &nbsp; &nbsp; 12pm-3pm&nbsp; &nbsp; &nbsp;34STORE&nbsp; &nbsp; &nbsp; 6pm-9pm&nbsp; &nbsp; &nbsp; &nbsp;78STORE&nbsp; &nbsp; &nbsp; 3pm-6pm&nbsp; &nbsp; &nbsp; &nbsp;10&nbsp;SCHOOL&nbsp; &nbsp; 9am-12pm&nbsp; &nbsp; 5&nbsp;SCHOOL&nbsp;&nbsp; 12pm-3pm&nbsp; &nbsp; &nbsp;120SCHOOL &nbsp; 6pm-9pm&nbsp; &nbsp; &nbsp; &nbsp;1SCHOOL &nbsp; 3pm-6pm&nbsp; &nbsp; &nbsp; &nbsp;2&nbsp;==================================&nbsp;I want to get the value&nbsp;9pm-12am against th BANK column,&nbsp;in a row using matrix visualization. and ignore the lesser time range. However when I attempt to do this in a measure using&nbsp;VAR peaktime= Calculate(LookupValue([Timerange],[CountofTimeRange],MAX([CountofTimeRange])),[Date].[month]&gt;= DATE(maxyear,maxmonth,1))receive an error which says&nbsp;A&nbsp;table of multiple values was supplied where a single value was expected.&nbsp;Using the Matrix visual the result should be something like this,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BANK&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;STORE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SCHOOLPeakTime&nbsp; &nbsp;&nbsp;9pm-12am&nbsp; &nbsp; &nbsp;9pm-12am&nbsp; &nbsp; &nbsp; &nbsp;12pm-3pm

albertoferrari · Answer

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:&nbsp;FTR = 
CONCATENATEX(
    TOPN (
        1, 
        ADDCOLUMNS(
            SUMMARIZE ( Ranges, Ranges[Name], Ranges[TimeRange] ),
            "Occurrences", CALCULATE( COUNTROWS ( Ranges ) )
        ),
        [Occurrences]
    ),
    Ranges[TimeRange],
    ","
)Have fun with DAX!Alberto Ferrarihttp://www.sqlbi.com

Forum Discussion

Most repeated text value for time range

3 Replies

Related content

Repeat Rate by Period Range Text

Bottom 10 considering repeated value.

Paginated Report headers not repeating

Cumulative Sum without repeat value

Count repeating values