Custom DAX table where rows show different counts of Tickets based on conditions

[Anonymous]

Hi,

Sorry about the cryptic subject but I'm not sure how to explain this in short. I have a data source with this structure:

Ticket	Levels	Reported date	Quarter	Resolved date
T-1234	1	01/01/2020	Q1	01/02/2020
T-5647	2	02/03/2020	Q1	05/03/2020
T-4632	2	03/03/2020	Q1	03/04/2020
T-6584	3	04/05/2020	Q2	10/05/2020
T-4638	1	05/06/2020	Q2
T-4937	1	06/07/2020	Q3	12/08/2020
T-2938	3	07/08/2020	Q3	25/08/2020
T-6437	2	08/09/2020	Q3

What I need to achieve is the following Table Visual in the report:

SLA	Level 1	Level 2	Level 3	Total
A	1	0	0	1
B	1	1	1	3
C	1	0	1	2
D	1	1	0	2

Where:

Row A = Return count of Tickets where "Reported date" is before selected Quarter AND "Resolved date" is in selected Quarter or is blank
Row B = Return count of Tickets where "Reported date" is in Selected Quarter
Row C = Return count of (all)Tickets where "Resolved date" is in Selected Quarter
Row D = Return count of (all)Tickets where "Resolved Date" is blank

- The values need to be dynamic, depending on Slicer 'Quarter' selection.

- The source table cannot be transformed (pivot/grouping..) as it is used in other Visuals with same slicer. In case of transformation needed, as I suppose this is the case, I would possibly add Index column -> then duplicate table -> create relationship 1to1 on Index -> make changes needed in TableCopy to be used for this visual.

Currently I'm using cards in each cell filterd by the requirements above, but it's not very practical. I'm hoping for a different solution. Looking forward to your suggestions 🙂

Thanks,

Pietro

[amitchandak · Accepted solution]

@Anonymous , Create an independent date table with Qtr. And select qtr in slicer. Have 4 measures like these and use show on  row in the matrix

Row A =
measure  = 
var _min = minx(allselected(Date), Date[Date])
var _max = maxx(allselected(Date), Date[Date])
return 
calculate(count(Table[Ticket]), filter(Table, Table[Reported date] <_min && Table[Resolved date] >=_min && Table[Resolved date] <=_min))


Row B =
measure  = 
var _min = minx(allselected(Date), Date[Date])
var _max = maxx(allselected(Date), Date[Date])
return 
calculate(count(Table[Ticket]), filter(Table, Table[Reported date] >=_min && Table[Reported date] <=_min))



Row C =
measure  = 
var _min = minx(allselected(Date), Date[Date])
var _max = maxx(allselected(Date), Date[Date])
return 
calculate(count(Table[Ticket]), filter(Table,Table[Resolved date] >=_min && Table[Resolved date] <=_min))


Row D =
measure  = 
var _min = minx(allselected(Date), Date[Date])
var _max = maxx(allselected(Date), Date[Date])
return 
calculate(count(Table[Ticket]), filter(Table, isblank(Table[Resolved date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

[Anonymous]

Thanks @amitchandak ! I wasn't aware of the "show on rows" functionality of the Matrix visual. Fantastic 🙂