Slicer with mm:ss field minute seconds filter

[dpombal]

Hi all,

I have an events dataset with these columns

Events Table creation in DAX

Events = DATATABLE(
"Row_ID",INTEGER,
"event",STRING,
"min",INTEGER,
"sec",INTEGER,
{
{1,"jump",0,0},
{2,"run",0,1},
{3,"climb",0,59},
{4,"jump",1,54},
{5,"rush",9,15},
{6,"jump",27,15},
{7,"sack",45,15},
{8,"run",50,46},
{9,"run",55,43},
{10,"run",70,19},
{11,"run" ,90,18}
}
)

I am trying to look for the most accurate/appropiate type for a field with  minute:seconds

something like 

mm:ss (for me hour or time is not valid because mm can be > 59 it is a soccer/rugby match duration time) and I would require an slicer/Slicer with mm:ss if possible/existing any custom visual/custom slider?

mm:ss

00:00   

15:43

70:15

90:30

My requirement is to have a minute:seconds smart filter/slider ideally like the one available for integer, number and date fields in powerbi

[v-alq-msft · Accepted solution]

Hi, @dpombal 

Based on your description, I have tried and not found a way to solve it with 'mm:ss' format. As a workaround, you may achieve your requirement with total seconds. You can create a calculated table and two calculated columns in Slicer table as follows.

Slicer = CROSSJOIN(
             SELECTCOLUMNS(
                 GENERATESERIES(0,MAX('Events'[min]),1),"Minutes",[Value]),
             SELECTCOLUMNS(
                 GENERATESERIES(0,59,1),"Second",[Value])
)
Show = [Minutes] & " : " & [Second]

Total = Slicer[Minutes] * 60 + Slicer[Second]

Then you may create a  calculated column in Events and create a relationship between 'Events' table and 'Slicer' table based on 'Total' columns.

Total = Events[min] * 60 + Events[sec]

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dpombal]

It is a workarond...although i wish a time slicer soon..thanks