dax formula help

[reije021]

I have the following data with the columns id, date en employee

an employee makes an id with a date

with dax formula max en min I calculate the days between the oldest en youngest date by id

Can you help me to calculate the average days by employee

youngest date = max(date)

oldest dat = min(date)

days = (youngest date - oldest date)*1

I need to calculate the average days by employee

id	date	employee
1	10-01-17	a
2	11-01-17	a
1	12-01-17	a
3	12-01-17	b
2	13-01-17	a
4	13-01-17	b
3	14-01-17	b
5	14-01-17	c
4	15-01-17	b
6	15-01-17	d
5	16-01-17	c
7	16-01-17	e
6	17-01-17	d
8	17-01-17	a
7	18-01-17	e
9	18-01-17	a
8	19-01-17	a
10	19-01-17	b
9	20-01-17	a
11	20-01-17	b
10	21-01-17	b
12	21-01-17	c
11	22-01-17	b
13	22-01-17	d
12	23-01-17	c
14	23-01-17	e
13	24-01-17	d
14	25-01-17	e
1	15-02-17	a
2	16-02-17	a
3	17-02-17	b
4	18-02-17	b
5	19-02-17	c
6	20-02-17	d
7	21-02-17	e
8	22-02-17	a
9	23-02-17	a
10	24-02-17	b
11	25-02-17	b

[skidambi2]

I have SQL Data where in i need to filter two feilds from the table and i used this query but i get error .

Total Tickets Received = CALCULATE(COUNT('Tickets Submitted'[incidentNumber]),FILTER('Tickets Submitted',('Tickets Submitted'[OwnerSupportOrg]="Global Service Desk",('Tickets Submitted'[status]<>"Cancelled"))))

Operator or expression '( )' is not supported in this context.

[Anonymous]

@skidambi2the expression you have supplied is a DAX statement, but your post indicates that you are trying to make a change to a Query?  Are you trying to use this DAX statement as part of the Power Query code in the Edit Queries section?

If you were hoping to use DAX and create a measure, i'd write it like this:

Total Tickets Received = CALCULATE(
	COUNTROWS('Tickets Submitted'),
	ALL('Tickets Submitted'),
	'Tickets Submitted'[OwnerSupportOrg] = "Global Service Desk",
	NOT 'Tickets Submitted'[status] - "Cancelled"
)

I've had to make assumptions on where you will want to use the measure and have assumed you need the ALL statement to ignore context.  You can remove the ALL row should you handle the filter context within the report page.

[skidambi2]

Thank you for the feedback

Actually i have sql data which is loaded in powerbi

i want to take the count of tickets by filtering two feilds

1) Owner Support Group - ServiceDesk

2) Status is equal to Cancelled

When i try to run your query i get this error

[skidambi2]

thank you so much but there are two problems

1) i tried to filter it based on certain feild the value does not change

2) Secondly i have removed another feild from the filter and tried but i get an error can you please have a look

Total Tickets Received = CALCULATE(COUNT('Tickets Submitted'[incidentNumber]),FILTER('Tickets Submitted',AND('Tickets Submitted'[OwnerSupportOrg]="Global Service Desk")))

Getting Error "Too few arguments were passed to the AND function. The minimum argument count for the function is 2."

[Anonymous]

As mentioned when i provided the solution, i have used the ALL statement which will ignore filter context, so that is why when you selected a specific value you noticed no difference.  In some cases removing the ALL statement will do the trick, but this could present you with additional problems depending on the context on how you have used the measure in your visuals.  Its likely you will need to use ALLEXCEPT instead.

From your last post, here is the statement you appear to be attempting to write

Total Tickets Received = 
CALCULATE(
	COUNTROWS('Tickets Submitted'),
	'Tickets Submitted'[OwnerSupportOrg] = "Global Service Desk"
)

or potentially this if you wanted to know a distinct count of the IDs

Total Tickets Received = 
CALCULATE(
	DISTINCTCOUNT('Tickets Submitted'[incidentNumber]),
	'Tickets Submitted'[OwnerSupportOrg] = "Global Service Desk"
)

[skidambi2]

Perfect worked fine

[Anonymous]

I've made a typo in my formula when i was writing it out.  I put a minus sign instead of an equals sign.  Try this instead:

Total Tickets Received = CALCULATE(
	COUNTROWS('Tickets Submitted'),
	ALL('Tickets Submitted'),
	'Tickets Submitted'[OwnerSupportOrg] = "Global Service Desk",
	NOT 'Tickets Submitted'[status] = "Cancelled"
)

[reije021]

additonal info

I need the average days a employee adds an ID

every id has value days and the id's made by the employee should give me the average days by employee calculated from the ID's the employee has made

[Ashish_Mathur]

Hi,

Try this

=(MAX(Data1[date])-MIN(Data1[date]))/DISTINCTCOUNT(Data1[id])

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=(MAX(Data1[date])-MIN(Data1[date]))/COUNTA(Data1[employee])

Hope this helps.

[Anonymous]

Try this measure:

Days = 
VAR startDate = FIRSTDATE('YourTable'[Date])
VAR endDate = LASTDATE('YourTable'[Date])
RETURN
floor(endDate - startDate, 1)

Put this into a matrix with your employee as the rows and this as your values.