topic Values in matrix based on wild characters in the categories and sub categories in DAX Commands and Tips

Values in matrix based on wild characters in the categories and sub categories

PC2790 — Mon, 24 Aug 2020 08:19:20 GMT

I am working on data which has the details of tickets received in a particular duration and I have created visualisations to reflect some menaingfule data.

My requirement is to categorise the tickets according to category and sub category in order to create a matrix and a line chart to reflect the trend of tickets received in a time series based on the matrix column and further create a time series graph for the selected value in slicer.

For example: I have categories such as Orange, red, green and sub categories as Orange pen,Orange ink, Red pen and Green pen.

The matrix should show the valuesin such a way that corressponding to sub categories there should be number of occurences accordingly.

--------Time Labels---

Orange 2

Orange pen 1

Orange ink 1

Red 1

Red pen 1

Green 1

Green pen 1

Something like this.

Can someone guide me how can this be achieved?

Pbix file here- https://drive.google.com/file/d/1HNmmmfZKLPNSMczcKsjwmMu3LA-KxA5n/view?usp=sharing

Any leads would be appreciated.

Thanks in advance

Re: Values in matrix based on wild characters in the categories and sub categories

Fowmy — Mon, 24 Aug 2020 08:30:59 GMT

PC2790

You can create a measure to count the number of tickets and add it to the matrix in the 2nd page. Let me know if this is not what is you are looking for.

Ticket Count = COUNTROWS(IncidentData)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Re: Values in matrix based on wild characters in the categories and sub categories

amitchandak — Mon, 24 Aug 2020 08:43:24 GMT

PC2790 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format?

Matrix display you can refer

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

Appreciate your Kudos.

Re: Values in matrix based on wild characters in the categories and sub categories

PC2790 — Mon, 24 Aug 2020 09:01:15 GMT

Thankyou for coming back...However there are two set of data that I have.

1) Incident data - containing field service offering

2) Service Offering Mapping - containing mapping of service and service offering.

That is the reason I can't simple user countrows as the logic should contain referring Service Offerin Mapping data to get the values from Incident data

Re: Values in matrix based on wild characters in the categories and sub categories

Sujit_Thakur — Mon, 24 Aug 2020 09:09:35 GMT

Dear PC2790 ,
Is this your desired outcome ??
let me know . if not please share a desired output made in excel.
ill help you acheive it .

Regards ,
Sujit

Re: Values in matrix based on wild characters in the categories and sub categories

PC2790 — Mon, 24 Aug 2020 09:31:22 GMT

Thankyou for the informative link that provides informatin about matrix.

Let me clarify my problem for you.

I have a table - Incidnet data containing all the details of the incidents received and in what category(Service Offering).

I want to create a matrix that would show me these service offerings according to years/months/weeks(I'll provide a slicer for the user to choose).

The requirement was to show a matrix with service offering field as sub category along with a high level classification on the basis of service which I want to use as a category.

To achieve this, I created another table - Service Offering mapping that contains the mapping of a Service(category) along with Service offering(sub category).

Now using these two tables, I want to show the matrix something like below:

2018 2019 2020

Category1

Subcategory 1

Category2

Subcategory 2

Subcategory 3

Category3

Subcategory 4

I hope I made the requirement clear.

Re: Values in matrix based on wild characters in the categories and sub categories

PC2790 — Mon, 24 Aug 2020 09:40:35 GMT

Sujit_Thakur something like this:

	2018	2019	2020
ASCM
Account Switcher Case Management - Production
BASE24
Base24 - Production

There should be values corresponding to year and category/subcategory

Re: Values in matrix based on wild characters in the categories and sub categories

Sujit_Thakur — Mon, 24 Aug 2020 12:09:55 GMT

Dear PC2790 ,
if this is what you wanted .

Then here is your solution

PBIX - https://drive.google.com/file/d/1yyMgWz8olWQez_mVzMYshtaRmLskG7Sw/view?usp=sharing

Please give kudos to all my post to keep me motivated .
And if this helped please select this post as an solution , it will help our other friends .

Happy to help , if any doubt in future let me know , tag me and post questions so that i can know you are asking something

regards ,
Thakur Sujit

Re: Values in matrix based on wild characters in the categories and sub categories

Sujit_Thakur — Tue, 25 Aug 2020 07:52:20 GMT

Dear PC2790 ,
if my post helped , please accept as solution .
kudos are always welcome

Re: Values in matrix based on wild characters in the categories and sub categories

PC2790 — Tue, 25 Aug 2020 14:22:43 GMT

Sujit_Thakur Thankyou much for your help. Accepted your response as a solution.
This is exactly what I wanted. I did not even realise that it was this simple.

Re: Values in matrix based on wild characters in the categories and sub categories

Sujit_Thakur — Tue, 25 Aug 2020 14:59:20 GMT

PC2790 happy to know you reached to your solution .

Keep learning 😀😀😀

Regards

Thakur Sujit