cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
veng2002 Frequent Visitor
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
Moderator v-sihou-msft
Moderator

Re: Matrix issues

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

 

 

veng2002 Frequent Visitor
Frequent Visitor

Re: Matrix issues

Hi Simon,

 

Thanks for your reply Smiley Happy

 

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,

Super User
Super User

Re: Matrix issues

Hi,

 

Share some data and show the expected result.

veng2002 Frequent Visitor
Frequent Visitor

Re: Matrix issues

Hi Ashishs,

 

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

Sample.jpgSample 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

Super User
Super User

Re: Matrix issues

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.

veng2002 Frequent Visitor
Frequent Visitor

Re: Matrix issues

Yes, I have 2 columns:

1 - Week # = Weeknum(Created)

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

Super User
Super User

Re: Matrix issues

Hi,

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 124 members 2,618 guests
Please welcome our newest community members: