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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
veng2002
Frequent Visitor

Matrix issues

Hi, 

 

I'm trying to accomplish multiple things here...

1) I have a status (row) for each record that I have (Open, ongoing, closed) and I'm trying to get a Matrix that will show the values for each status per week (column).

Problem 1 = if I have a zero (or multiple) it will not display the status. I need the status to be "static" and if there's a zero, to present it.

2) I'm trying to show the last 5 weeks of creaed records. My data starts on January, so the week count is at week 46 so far. I'm looking forward to show. My columns would be: Week 42 | Week 43 | Week 44 | Week 45 | Week 46

Problem 2 = I've been able to do this only manually at the visual filter. I have not been able to figure out a dynamic way for this.

 

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and show the expected result.


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

Hi Ashishs,

 

I'm attaching an image since as a regular user I'm not able to attach files to the forum:

Sample DataSample Data

 

From the above I need to build a Matrix that looks like:

 

 Week 41Week 42Week 43Week 44Week 45Week 46
Completed835012
Invalid101032
Opened173847
Ongoing556473857781

 

Where the first issue is that I cant get zeros in my matrix, it shows a blank cell.

 

Like the one on my previous response.

 

The second question, is to see how can I make my report to only show the last 5 weeks without the use of a slicer, that's why I mentioned maybe a calculation that counts the number of weeks?

 

And another and bran new issue that I have, is that the row "ongoing" is the sum of open problems up to that week, this last calculation I have not figure how to make it either... 

 

Hope this helps in explaining what I'm looking for.

Thanks and regards,

Vicente

Hi,

 

My first doubt is - How do you determine the week number?  Do you use the WEEKNUM() function?  Is the week number determined with respect to the Created date/Closed Date?  Plesae clarify.


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

Yes, I have 2 columns:

1 - Week # = Weeknum(Created)

2 - Week = "Week " & 'Week #'

Hi,

 

Share the link from where i can download the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-sihou-msft
Employee
Employee

@veng2002

 

1. I assume your Value for each status per week is a measure. Then you can apply IF condition like: 

 

=IF([Value]=BLANK(),"static",[Value])

2. You can use relative Date Slicer. 

 

33.PNG

 

Regards,

 

 

Hi Simon,

 

Thanks for your reply 🙂

 

1. Not working. Yes, my value is done with a measure. Using your approach does not give me the zero values, from your formula is checking if I'll get a blank value, but my status rows will always have a value. Dont know if you're measuring on a different way - maybe counting each status?

 

 Screenshot_1.jpg

 

2. This is a great slicer, didn't know about it and I think it's awesome. However, even though I'll use it I did not explain myself correctly. What I want to accomplish automatically is that the matrix only shows the last 5 weeks but without the use of a slicer. I dont know if building a measure that somehow calculates the number of existing weeks and substract the number -5? So the matrix updates every week and only shows the data for the last 5?

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.