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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pbilearning
Helper I
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?matrix snapshot.JPG

txtnw screenshot.JPG

 

 

1 ACCEPTED SOLUTION

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/

View solution in original post

14 REPLIES 14
parry2k
Super User
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.

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?

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

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

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/

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?

Hi,

Is this the result you are expecting?

Untitled.png

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/

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.

Hi,

You are welcome.  Does this work?

Untitled.png


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

hi @Ashish_Mathur 

 

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

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/

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.

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"

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors