Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 , 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
Thanks @amitchandak ! I wasn't aware of the "show on rows" functionality of the Matrix visual. Fantastic 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |