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
Anonymous
Not applicable

Matrix visualization get count total of field for each row ignoring some filters

Hi everyone,

 

I am working on a Matrix visualization hoping to get to a point like the below:

IDErrorAErrorBErrorCErrorDErrorEErrorFTotalTimestamps
1000001288
20010000288
30005005288

 

The underlying data is like:

IDTimestampErrorAErrorBErrorCErrorDErrorEErrorF
111/1/19 12 AM000000
111/1/19 12:05 AM000001
111/1/19 12:10 AM000000

(There are timestamps at 5 minute intervals for each ID)

 

I added a custom column Errors that is True if ErrorA+ErrorB+ErrorC+ErrorD+ErrorE+ErrorF > 0 and false otherwise.

 

There are two filters: one is applied to a timestamp field (like 11/1/2019 12:00 AM) to restrict to a range of dates, and the other is to show only those rows where Errors is True.

 

I am having a difficult time getting the TotalTimestamps measure to work. The goal is for it to be the total number of timestamps for a given ID within the specified date range regardless of whether there is an error or not. For example, if the date range is set to one day (say 11/1/2019) and a row exists for each 5 minute timestamp within the 24 hour interval, TotalTimestamps should be 288 (=5 minutes * 12 5-minute intervals per hour * 24 hours in one day). If, though, we only have data for 6 out of the 24 hours, TotalTimeStamps should be 360 (=5 minutes * 12 5-minute intervals per hour * 6 hours). 

 

This is what I've tried (among many, many other variations, all of which give the same result): CALCULATE(COUNTROWS('Data'[Timestamp]),ALLEXCEPT('Data','Data'[Timestamp],'Data'[ID])).

 

However, this only counts those rows for which Errors is TRUE--the Errors filter seems to not be unapplied.

 

Is there a way to get to what I want?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Giotto,

Thanks so much for your reply--you got it almost right. I was able to take what you had and modify it slightly:

 

TotalTimestamps = CALCULATE(COUNT('Table'[Timestamp]),ALLEXCEPT('Table','Table'[ID], 'Table'[DateSlicer]))
 
Then I added a visual filter on Errors to the visualization. This produced the expected results.
 
powerbi-capture.png

 

I explored a little more, and uncovered that the primary reason for my attmept not working was due to using the Timestamp field directly. If you change the slicer from the DateSlicer field to the Timestamp field, change the slicer type to "Between", and change TotalTimestamps to 
 
TotalTimestamps = CALCULATE(COUNT('Table'[Timestamp]),ALLEXCEPT('Table','Table'[ID], 'Table'[Timestamp]))
 
you will get the same incorrect results that I was getting in all of my futile attempts. I think this may be a bug, as I'm not sure why using a date/time combo instead of strictly date would change the observed output.
 
Is there a place to report this for further investigation?
 
powerbi-capture2.png

 

Many thanks again for your response and help!
 
 

View solution in original post

2 REPLIES 2
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, I create a table to test:

81.png

Please take following steps:

1)Create a column to filter rows:

Errors = IF(([ErrorA]+[ErrorB]+[ErrorC]+[ErrorD]+[ErrorE]+[ErrorF])>0,true,false)

2)Create a measure to calculate total time stamps:

TotalTimestamps = CALCULATE(COUNT('Table'[Timestamp]),ALLEXCEPT('Table','Table'[ID]),FILTER('Table','Table'[Errors]=true))

              Note that you should apply ‘Errors’ filter in measure instead of filter pane.

3)Create a column as date filter:

DateSlicer = 'Table'[Timestamp].[Date]

4)Choose one day and the result shows:

82.png83.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

Anonymous
Not applicable

Hi Giotto,

Thanks so much for your reply--you got it almost right. I was able to take what you had and modify it slightly:

 

TotalTimestamps = CALCULATE(COUNT('Table'[Timestamp]),ALLEXCEPT('Table','Table'[ID], 'Table'[DateSlicer]))
 
Then I added a visual filter on Errors to the visualization. This produced the expected results.
 
powerbi-capture.png

 

I explored a little more, and uncovered that the primary reason for my attmept not working was due to using the Timestamp field directly. If you change the slicer from the DateSlicer field to the Timestamp field, change the slicer type to "Between", and change TotalTimestamps to 
 
TotalTimestamps = CALCULATE(COUNT('Table'[Timestamp]),ALLEXCEPT('Table','Table'[ID], 'Table'[Timestamp]))
 
you will get the same incorrect results that I was getting in all of my futile attempts. I think this may be a bug, as I'm not sure why using a date/time combo instead of strictly date would change the observed output.
 
Is there a place to report this for further investigation?
 
powerbi-capture2.png

 

Many thanks again for your response and help!
 
 

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.

July Newsletter

Fabric Community Update - July 2024

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