DAC simple measure help - conditional count

[tomshaw83]

Hi there,

I am after some help with a dax measure, I have 2 tables similar to the below that I am importing into the front end:-

Employee Table

Name	Method of Travel	Time to Work
John Doe	Car	04:01:56
Simon Shreeves	Cycle	01:01:56
Lucy Jones	Walk	00:30:24
Paul Smith	Walk	00:43:21
Carl Morgan	Car	01:23:34

Method Average Table

Method of Travel	Average	+1%	+2%	+3%
Car	01:00:00	01:06:00	01:12:00	01:18:00
Walk	00:30:00	00:33:00	00:36:00	00:39:00
Cycle	00:40:00	00:44:00	00:48:00	00:52:00

I am after a table in the front end that looks like the below, the count is based on a dynamic measure so I can use a slicer for (Average, 1%, 2%, 3%) and it recalculates accordingly. Effectively if Time to work <= (average, 1%, 2%, 3%) then count

Method of Travel	Count
Car	1
Walk	1
Cycle	0

Any help on this would be greatly appreciated

Thanks

Tom

[tomshaw83]

Ok, thank you, based on the assumptions above, could you give an idea what the DAX code would look like for the Travel count measure - i'm used to working with IF statements in excel, but don't know where to start in DAX

[Anonymous]

Instead of working with times, it's much easier to work with integers. Just convert the times into the total number of minutes (since you're not interested in seconds, as much as I cas see). Secondly, the second table of yours is pivoted. Unpivot it and make the columns' names (Average + 1%, +2%,...) an attribute's value and the time (integer) another attribute's value. This is how it should be done according to the philosophy of PBI. Once you have this design, it'll be dead easy to write measure(s) you're after.