Filter

[khalidjou]

Hi all, 

I have a table with date and time. I want to exlude the rows with time = 00:00:00 to compute the max Occupation.

I've written a mesure that gives the maximum occupation but with time = 00:00:00 included

Max_occup =

var Datum= DATE([max_Année];[max_month];[max_jour])

var Bi=CALCULATE(MAXA(tblname[Occupation]);tblname[Date]=Datum)

return

Bi

*************************NB: I am on direct query

************************************

Thank you

[v-jingzhang · Accepted solution]

Hi @khalidjou 

You can try this measure

Max_occup =
VAR Datum =
    DATE ( [max_Année]; [max_month]; [max_jour] )
VAR Bi =
    CALCULATE (
        MAXA ( tblname[Occupation] );
        tblname[Date] = Datum;
        TIMEVALUE ( tblname[Date] ) <> TIME ( 0; 0; 0 )
    )
RETURN
    Bi

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@khalidjou , You need like

new meausre =
CALCULATE(MAXA(tblname[Occupation]);filter(allselected(tblname) , tblname[Date]=max(tblname[Date]) && timevalues([tblname[Date]) <> time(0,0,0)))

or only date part of the date

Only date column =  datevalue([Date])

new meausre =
CALCULATE(MAXA(tblname[Occupation]);filter(allselected(tblname) , tblname[Only Date]=max(tblname[Only Date]) && timevalues([tblname[Date]) <> time(0,0,0)))

[khalidjou]

Thank you Amitchandak, 

Another question please. When I finf the Max occupation, how can I get the corresponding date ?