cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leeny127 Frequent Visitor
Frequent Visitor

Matrix Values are not Affected by Date Slicer

Hello - I have a matrix that shows "# of Packages Reviewed" (and other values seen below) for various people. The values are all calculated by measures.  Next, I have a "Months" slicer that should filter all values for each person that month. The source tables for the slicer and matrix are tied by a relationship.

 

Problem: the slicer does not seem to effect the measure values.

Example (image 1😞 the "# of Packages Reviewed" for Cam shows 2, which is the total number he has reviewed for all months. The filter is set to July and should only show 1 package for Cam.

 

I have searched the forums but no solution seems to work for my scenario. Any guidance is appreciated!

 

Here are the measures used:

# Packages Reviewed:  COUNTX(FILTER('Count_Paralender_Merge','Count_Paralender_Merge'[Paralender Unique Value]>0),'Count_Paralender_Merge'[Paralender Unique Value])

# of Errors = IF('Count_Paralender_Merge'[Yes No #] = 0,'Count_Paralender_Merge'[Count of Y/N Values],0)

# of Fields Possible = IF('Count_Paralender_Merge'[Yes No #] = 1,'Count_Paralender_Merge'[# of Fields],0)

Error% = DIVIDE(SUM('Count_Paralender_Merge'[# of Errors]),SUM('Count_Paralender_Merge'[# of Fields Possible]))

# Correct = IF('Count_Paralender_Merge'[Yes No #] = 1,'Count_Paralender_Merge'[Count of Y/N Values],0)

 

Image 1

Visuals.PNG 

 

Image 2 - CALENDAR Table

 

5_CALENDAR Table.PNG 

 

Image 3 - Table Relationships

   6_Table Relationship.PNG

5 REPLIES 5
Super User IV
Super User IV

Re: Matrix Values are not Affected by Date Slicer

Are you sure that the direction of the relationship is correct?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Leeny127 Frequent Visitor
Frequent Visitor

Re: Matrix Values are not Affected by Date Slicer

@Greg_Deckler I believe so...when I change the Cross filter direction, it still has no effect on the values but does mess up the 'Months' visual display (only showing two months in the slicer).

 

It has to be something with the measures because the date slicer does filter the matrix Rows (Team and PL Name); it just doesn't filter the values...for instance:

 

Cam has reviewed 2 packages

- one in July  - one in Aug

 

When the 'July' filter is applied, the matrix shows both of Cam's reviews

When the 'Aug' filter is applied, the matrix shows none of his reviews.

Comparison.PNG

 

So the measure is counting all of the person's package reviews and displaying them only in the first month they did them.

Microsoft v-danhe-msft
Microsoft

Re: Matrix Values are not Affected by Date Slicer

Hi @Leeny127,

Could you please share the pbix file is possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Leeny127 Frequent Visitor
Frequent Visitor

Re: Matrix Values are not Affected by Date Slicer

@v-danhe-msft Thank you for your reply.  I am unable to share the pbix per my company's security rules.

Leeny127 Frequent Visitor
Frequent Visitor

Re: Matrix Values are not Affected by Date Slicer

@Greg_DecklerAfter reading more similar posts, I think my error is not referencing the related table within the formula...but I'm still lost on how to make it work.  Here's what I have so far:

 

Table 1 - has IDs and dates (ocurring multiple times)

Table 2 - has column of unique dates and the month in text

Table 1 & 2 are related by their date columns, Many : 1

 

I have two measures:

1) Count of IDs = COUNTX (FILTER ('Table_1', 'Table_1[ID] > 0), 'Table_1'[ID])

2) Filtered by Month = CALCULATE ('Table_1'[Count of IDs], ALLSELECTED ('Table_2'[Month]) )

 

Visuals:

Slicer - Table_2 [Month]

Matrix - Table_1 [ID] as Rows; (Measure 2) 'Filtered by Month' as a value

 

How can I get the Month selected in the slicer to filter the Measure 2 values for only that month selected?

 

EXAMPLE...

 

SLICER

January  February  March  April               ------->           January  February  March  April

 

MATRIX (No month selected in slicer)                          MATRIX (January selected in slicer)

 ID      Measure 2 - Filtered by Month      ------->          ID      Measure 2 - Filtered by Month

 1.1        1                                                                        1.1        1    id 1 only showed up in Table_1 one time for January

 2.7        1                                                                        6.5        2    id 6 only showed up in Table_1 two times for January

 6.5        3                                                                                            ids 2.7 & 9.1 didn't show up at all for month of January

 9.1        2

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors