Forum Discussion

Zain26's avatar
Zain26
Advocate II
8 years ago

Most repeated text value for time range

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.  ...
Need Help
Show and Tell
AlbertoFerrari's avatar
AlbertoFerrari
Most Valuable Professional
8 years ago

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