Can I change Row and Column months separately in a matrix

[pbilearning]

Hi everyone,

I am stuck with the below issue. It would be great, if anyone can help me on this please

I am currently working on a data where I have to calculate count of number of id in categories mentioned as Zero, One, Down, Neutral.
Inorder to do so, I am creating a matrix with count of ids divided in different category (as shown below) But giving an option of dropdown for user to select month to compare for rows and columns separetely. Like columns are july and rows are for august Is that possible? Can we give different month filters for the matrix rows and columns?

[parry2k]

@pbilearning not sure what you mean? can you explain bit more?

Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com 

[pbilearning]

Hi @parry2k 

I am trying to create a matrix with count of id as values and categories in rows and columns. I want to create month slicer for them like july month for rows And Aug month for columns. Is it possible to create different slicers for rows and columns in matrix so that I can choose months according to my requirements, instead of just fixing my results to two specific months?

[parry2k]

@pbilearning I'm sure it can be done with some tweaking/hacking but the question is if the data source is the same, what you expect the result when you filtering rows and columns on different month, isn't data source for both row and column is the same. your requirement is still a bit confusing, may throw some sample data and expected output.

[pbilearning]

@parry2k I have provided link for your reference. 

I have created category and counts in matrix but I am confused how to create option to select rows and column values w.r.t to month selected for them. I am just able to create common slicer of month for the entire matrix instead of creating custom slicer with row and column values provided based on different months selected from the slicer. 
PBI File 

Excel File 

Requesting for help 🙂

[Ashish_Mathur]

Hi,

Is your ultimate objective to know "How many ID's that were Down in August were Neutral in July"?

[pbilearning]

Yes, I want to check those counts but also want to provide an option to user to select the month they want to compare with, instead of just fixing it to specific month. Is that possible?

[Ashish_Mathur]

Hi,

Is this the result you are expecting?

The way to interpret the figures in the red rectange is:

1. 3 participants were in the Down Category in June 2019
2. Of those 3 participants, 2 remained in the same category in July and 1 moved to Zero
3. Of those 3 participants, 1 remained in the same category in July and 2 moved to Neutral

We can obviously filter the months in the row and column labels to show the ones that you want.

Is this what you are expecting?  Are the figures there correct?

[pbilearning]

Hi @Ashish_Mathur  Yes!! thank you so much :). This View is amazing ! 🙂 But I need an option for users to select months for rows and column labels according to their choices. Is that possible. Because it is showing everything for now, but if we want to give our choice for months in row and column labels. Can we do that? Requesting for your help please.

[Ashish_Mathur]

Hi,

You are welcome.  Does this work?

[pbilearning]

hi @Ashish_Mathur 

Yes!!!. This is great!!. this would work for me. Can you please send me this file , if you dont mind ? 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-shex-msft]

HI @pbilearning,

As parry2k said, it is possible but it can't be achieved by normal usage. You need to do some additional trick to achieve this.
Firstly, you need to extract the month values to a new table. (notice: these tables do not contain relationships)

Then you can use the original table month and new table month field on matrix visual column and row fields to design your visualizations.
For the matrix value field, you can write a measure formula to manually calculate the result based on the current row and column label.

Notice: you can't use one table fields to extract multiple selection results even if you use this field to create multiple filters/slicers.

Regards,

Xiaoxin Sheng

[pbilearning]

Hi @v-shex-msft 

I tried to create a separate table with only months and not creating relationship between them. Once done with that I started with matrix creation by selecting the months from original table and newly created table but I am getting an error as "Cant determine the relationship between two fields"

[v-shex-msft]

HI @pbilearning,

In fact, this issue is caused by your row and column field does not contain a relationship. (power bi can't analyst correspond matrix graph based on existed fields)
For this scenario, you can add the measure which calculates based on current row and column label on value fields to fix this issue.

Regards,

Xiaoxin Sheng