cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PDV
Helper I
Helper I

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

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             LevelsReported dateQuarterResolved date
T-1234101/01/2020Q101/02/2020
T-5647202/03/2020Q105/03/2020
T-4632203/03/2020Q103/04/2020
T-6584304/05/2020Q210/05/2020
T-4638105/06/2020Q2 
T-4937106/07/2020Q312/08/2020
T-2938307/08/2020Q325/08/2020
T-6437208/09/2020Q3 

 

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

 

SLALevel 1Level 2Level 3Total
1001
B1113
C1012
D1102

 

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@PDV , 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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@PDV , 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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors