Return highest value in child table and count higher than x number

[SteveDoherty]

I have the following data:

Table: Ticket

TicketID
1
2
3
4
5
6
7
8
9
10

Table: Time

TimeID	TicketID	Value
1	1	23
2	1	50
3	1	54
4	1	10
5	2	87
6	2	32
7	2	45
8	2	5
9	3	10
10	3	20
11	4	43
12	4	37
13	4	51
14	5	95
15	5	38
16	5	73
17	5	61
18	6	67
19	6	52
20	7	62
21	7	67
22	8	13
23	8	50
24	9	64
25	10	17

What I need to do is get the highest value from the Time table against the TicketID from the Tickets table.

This would give me the following:

TicketID	Max of Value
1	54
2	87
3	20
4	51
5	95
6	67
7	67
8	50
9	64
10	17

I can achieve that by putting a 1-* relationship between TicketID in Tickets and TicketID in Time, and then adding TicketID from Tickets and Value from Time into a table, and then setting the visual to Max on the Value. However, this just filters the visual and not the underlying data, so when I try to do my next calculation of how many values are higher than 50, it looks at all the values in the Time table instead of just the highest filtered by TicketID.

I wrote the following this works, but only in Import mode:

MaxValue = CALCULATE(MAX('Time'[Value]),FILTER('Time','Time'[TicketID]=EARLIER(Tickets[TicketID])))

Can anyone help me with what I'm trying to acheive? I need this to work in DirectQuery or have a workaround.

To summarise:

Given the data above, I need to obtain the highest number in the value for each TicketID in the Time table and then count how many are higher than 50. This would give me a count of TicketID with the highest value bigger than 50.

Thanks in advance

Steve

[SteveDoherty · Accepted solution]

Hi everyone,

Thank you so much for your responses on this. It's the first time I've asked a question on this forum and I was humbled with the prompt responses.

Unfortuantely the proposed solutions didn't provide the correct output when in DirectQuery mode, so I have gone to the SQL database and created a view directly on there to pull the correct data and then I'm using Power BI to model it.

Thanks again

Steve

[amitchandak]

@SteveDoherty , Create a column in ticket table

MaxValue = MAXX(FILTER('Time','Time'[TicketID]=Tickets[TicketID]),'Time'[Value])

[ryan_mayu]

@SteveDoherty 

try to create a column.

Column = MAXX(FILTER('time','time'[TicketID]=ticket[TicketID]),'time'[Value])

then create a measure

Measure = CALCULATE(COUNTROWS(ticket),FILTER(ticket,ticket[Column]>50))

 

[SteveDoherty]

Thank you for your reply.

This isn't working because the Ticket table and Time tables are 1 - many, so the expression cannot find a single row from the Time table.

This works in Import mode, but I cannot use it in DirectQuery mode, as I get this error:

"Function 'MAXX' is not allowed as part of calculated column DAX expressions on DirectQuery models."

[Anonymous]

Hi @SteveDoherty,

AFAIK, power bi has usage limit on calculating table/columns when you design in direct query mode. (most of DAX functions has been limited to use)
If you want to add a field to your parent table, I'd like to suggest you add tsql statement to your connector to look up child table records and return the highest one as a custom field.

Pull Data from SQL Server with Power BI Designer 

Regards,

Xiaoxin Sheng

[SteveDoherty]

Hi,

That is what I was thinking I might have to do, but I've not had any experience in T-SQL yet, so it will be a bit of a learning curve. Are there any examples I can find or you could provide to help me with getting started on that?

Thanks

[Greg_Deckler]

@SteveDoherty - Try this:

MaxValue =

  VAR __TicketID = MAX(Tickets[TicketID])

RETURN

  CALCULATE(MAX('Time'[Value]),FILTER('Time','Time'[TicketID]=__TicketID))

Or

MaxValue =

  VAR __TicketID = MAX(Tickets[TicketID])

RETURN

  MAXX(FILTER('Time','Time'[TicketID]=__TicketID),'Time'[Value])