Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Can I change Row and Column months separately in a matrix

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?

1 ACCEPTED SOLUTION
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
14 REPLIES 14
Super User

@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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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?

Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

@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

Requesting for help 🙂

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper I

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?

Super User

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?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper I

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.

Super User

Hi,

You are welcome.  Does this work?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper I

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

Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper I

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"

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors