Forum Discussion
How to return Distinct rows based on multiple values
I have recently been given a project at work which requires the use of DAX (I am a complete newbie) within PowerBI and, whilst i think that i have the basic measures dealing with current data calculated as expected I am having issues when looking back historically. The source dataset is coming from SQL Server and is comprised of data relating to ticket updates from our work logging software. Each ticket can have multiple updates throughout the day but , for my current purpose, i am looking to plot the values over time (e,g tickets opened per day) and so only need to count a distinct ticket once per day. This is pretty straigh forward using Distinct count but where i am really struggling is when i need to reference other values from that distinct row(e.g. time in status) which articfically inflate the sums when duplicated. I SQL i could do this using Row_Number by ticket and date and then picking the last row for each ticket/day but in DAX, from my understanding at least, functions such as RANKX only allow you to rank by a single value?
Example Data
Ticket Number, UpdatedTime, Status, Time In Status, Severity
201546, 2017-07-28 09:00,Open, 10, 20
201546, 2017-07-28 10:00, InProgress, 70,20
Where i would want to filter to only the last row and extract the severity etc
Any help/pointers/advice anyone could provide would be much appreciated as i am working to tight timelines.
Hey,
I created a little example, that uses the sample data + one extra ticketnumber.
What you achieve by the windowing function ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) is achieved by this calculated column, except the filtering this comes a little later
IsMaxUpdatedTime = IF('TicketStatus'[UpdatedTime] = CALCULATE( MAX('TicketStatus'[UpdatedTime]), ALLEXCEPT('TicketStatus',TicketStatus[Ticket Number],TicketStatus[UpdatedDate]) ),"Yes", "No")This column can now be used as a slicer, or in any other visual If you do not want to expose this column to your user (just hide it from report view) , you can also use this in any CALCULATE statement like this CALCULATE(<expression>, 'TicketStatus'[UpdatedTime] = "yes")
Because I'm calculating a column I have to be aware, that there is just a ROW CONTEXT. This means that just using
MAX('TicketStatus'[UpdatedTime])will always return the value of the current row. Knowing this I have to introduce a FILTER CONTEXT this is done by encapsulating the the MAX( into a CALCULATE(. Still MAX would return the value of the current row, because the current row works as a filter, but now I'm able to use the power of CALCULATE to expand the current FILTER CONTEXT the current row. This is done by
ALLEXCEPT('TicketStatus',TicketStatus[Ticket Number],TicketStatus[UpdatedDate])Using this functions removes the filter from all the columns of the table, except [Ticket Number] and the date column (this column is necessary as you already mentioned, this column just contains the date without any time information.
Now I'm using the result of the calculate statement to check if the returned date value equals the date value of the current row. If this is the case I know that I found the latest datetime for ticketnumber and date.
Now I'm able to use this column in my report.
In the sample report I used the column as a slicer, and turned the interaction with the bar chart off by
1. mark the slicer and switch "Edit Interaction" on
2. then I disabled the filter interaction of the bar chart
3 finally i added my new calculated column as an visual level filter to the bar chart to ensure that the bar chart visual becomes independent from the slicer and just uses the rows that are flagged ismaxupdatedtime = yes
Done :-)
Hope this helps
5 Replies
Hey,
indeed sometimes it can become difficult to acccess values in sequence of events, but maybe this may help.
First I would create a calucultated column that flags the row that has the max date for each ticket like so:
is latest status =
IF( CALCULATE( MAX('#yourtable'[UpdatedTime]), ALLEXCEPT('#yourtable','#yourtable'[Ticket Number],'#yourtable'[UpdatedTime]) )='#yourtable'[UpdatedTime]
, "yes"
, "No")This new column can than be used to filter the rows, either as slicer or as report level filter.
Whenever I have to analyze a sequence of events, I a add a rowindex to the table, before I can do that in Power Query I have to order the table, in your case
- [TicketNumber]
- [UpdatedTime]
Than I'm simply add an Index Colum (1 based) and use the above method to add some columns with sequence information
- no of events for a ticket
- no of the first event
- no of the last event
This makes it possible to use the LookupValue Function to create further insight.
Hope this helps
Thank you for your prompt responses and my apologies for the delay in mine. After playing around with both of these suggestions, whilst they both appear to give me singular values per ticket as i believe was intended, i fear that i may not have been particularly clear in my explanation and lack the skills to be able to develop these further. What i am looking for is the last record per day per ticket, not the latest record per ticket overalll e.g.:
Ticket Number, UpdatedTime, Status, Time In Status, Severity
201546, 2017-07-28 09:00,Open, 10, 20
201546, 2017-07-28 10:00, InProgress, 80,20
201546, 2017-07-29 01:00,In QA,100 , 20
201546, 2017-07-29 22:00, InProgress, 30,20
So in this example (ignore the time in status numbers they were randomly picked) i would be looking to return the last record on the 28th AND the last row on the 29th to give me the latest status/time in status value for that day. The aim is to be able to plot, by date, a bar/liine chart which will show how long tickets are spending in each status by day.
I also have a date column available to me (minus the time) and in sql the equivalent of what i am trying to achieve could be created using something similar to:
ROW_NUMBER () OVER (PARTITION BY [Ticket Number],Dateiso order by [Ticket Number],dateiso, UpdatedTime DESC) as RN and then selecting only the top row per day (rn=1)
Hey,
I created a little example, that uses the sample data + one extra ticketnumber.
What you achieve by the windowing function ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) is achieved by this calculated column, except the filtering this comes a little later
IsMaxUpdatedTime = IF('TicketStatus'[UpdatedTime] = CALCULATE( MAX('TicketStatus'[UpdatedTime]), ALLEXCEPT('TicketStatus',TicketStatus[Ticket Number],TicketStatus[UpdatedDate]) ),"Yes", "No")This column can now be used as a slicer, or in any other visual If you do not want to expose this column to your user (just hide it from report view) , you can also use this in any CALCULATE statement like this CALCULATE(<expression>, 'TicketStatus'[UpdatedTime] = "yes")
Because I'm calculating a column I have to be aware, that there is just a ROW CONTEXT. This means that just using
MAX('TicketStatus'[UpdatedTime])will always return the value of the current row. Knowing this I have to introduce a FILTER CONTEXT this is done by encapsulating the the MAX( into a CALCULATE(. Still MAX would return the value of the current row, because the current row works as a filter, but now I'm able to use the power of CALCULATE to expand the current FILTER CONTEXT the current row. This is done by
ALLEXCEPT('TicketStatus',TicketStatus[Ticket Number],TicketStatus[UpdatedDate])Using this functions removes the filter from all the columns of the table, except [Ticket Number] and the date column (this column is necessary as you already mentioned, this column just contains the date without any time information.
Now I'm using the result of the calculate statement to check if the returned date value equals the date value of the current row. If this is the case I know that I found the latest datetime for ticketnumber and date.
Now I'm able to use this column in my report.
In the sample report I used the column as a slicer, and turned the interaction with the bar chart off by
1. mark the slicer and switch "Edit Interaction" on
2. then I disabled the filter interaction of the bar chart
3 finally i added my new calculated column as an visual level filter to the bar chart to ensure that the bar chart visual becomes independent from the slicer and just uses the rows that are flagged ismaxupdatedtime = yes
Done :-)
Hope this helps
