Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during AI Skills Fest. This week only. Secure your voucher now.

Reply
Anonymous
Not applicable

FILTER TABLE COLLUMN BY COLUMN VALUE IN RELATED TABLE

‎09-30-2021 04:55 AM

Good day, 

 

I need help making filter measure as I cannot make it work. I have two queries related to each other: 

 

QUERY 1 - Summary Of Findings:                                

 

SOURCENAME OF SHOPRating
Source 1Shop 15
Source 2Shop 12
Source 3Shop 21
Source 4Shop 22
Source 5Shop 33
Source 6Shop 34
Source 7Shop 15
Source 8Shop 13
Source 9Shop 23
Source 10Shop 26
Source 11Shop 37
Source 12Shop 38
Source 13Shop 14
Source 14Shop 13
Source 15Shop 26

 

Questy 2 - Date of Rating Report 

 

SOURCEName of Shop Date Of RatingLatest
Source 1Shop 112/1/2020Not Latest
Source 2Shop 112/2/2020Not Latest
Source 3Shop 212/3/2020Not Latest
Source 4Shop 212/4/2020Not Latest
Source 5Shop 312/5/2020Not Latest
Source 6Shop 312/6/2020Not Latest
Source 7Shop 112/7/2020Not Latest
Source 8Shop 112/8/2020Not Latest
Source 9Shop 212/9/2020Not Latest
Source 10Shop 212/10/2020Not Latest
Source 11Shop 312/11/2020Not Latest
Source 12Shop 312/12/2020Latest
Source 13Shop 112/13/2020Not Latest
Source 14Shop 112/14/2020Latest
Source 15Shop 212/15/2020LAtest

 

questies have relationships over SOURCE column. What I'm looking for is the measure  to Filter out "SUMMARY OF FINDINGS" [RATING] by DATE OF REPORT [LATEST]. 

 

I want measure which will show me uniques ratings for each shop based on LATEST report! 

 

Thnaks

Aleks 

Labels:
Message 1 of 4
996 Views
0
1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage
In response to Anonymous

‎09-30-2021 06:15 AM

OK.  Here's a couple of options for you.

 

Measures:

Latest Rating = CALCULATE(SELECTEDVALUE('Summary of Findings'[Rating]), 'Date of Rating Report'[Latest] = "Latest")

Previous Rating = 
VAR _ShopWithPreviousDate = 
ADDCOLUMNS(
    VALUES('Date of Rating Report'[Name of Shop ]),
    "@Previous", CALCULATE(MAX('Date of Rating Report'[Date Of Rating]), 'Date of Rating Report'[Latest] <> "Latest")
)
VAR _Result = 
CALCULATE(
    MAX('Summary of Findings'[Rating]),
    TREATAS(_ShopWithPreviousDate, 'Date of Rating Report'[Name of Shop ], 'Date of Rating Report'[Date Of Rating])
)
RETURN 
	_Result

Avg Rating = AVERAGE('Summary of Findings'[Rating])

 

This first chart compares Latest Rating with the average of all previous ratings per shop

PaulOlding_0-1633007493892.png

 

... and this one compares the latest rating with the most recent previous rating.

PaulOlding_1-1633007544487.png

 

 

View solution in original post

Message 4 of 4
910 Views
3 REPLIES 3
PaulOlding
Solution Sage
Solution Sage

‎09-30-2021 05:19 AM

Hi,

 

Why do you want a measure to do this?

You could add a slicer or filter to the report on the Latest column instead.

 

Message 2 of 4
951 Views
0
Anonymous
Not applicable
In response to PaulOlding

‎09-30-2021 05:22 AM

Hi, 

 

I want to combine Shop rating for Latest Report and not Latest report in one Visual Chart  whre name of shop will be X axis and Y axis will be ratings. So I can see difference between prev. and last at the glance. 

 

Cheers

Aleks 

Message 3 of 4
933 Views
0
PaulOlding
Solution Sage
Solution Sage
In response to Anonymous

‎09-30-2021 06:15 AM

OK.  Here's a couple of options for you.

 

Measures:

Latest Rating = CALCULATE(SELECTEDVALUE('Summary of Findings'[Rating]), 'Date of Rating Report'[Latest] = "Latest")

Previous Rating = 
VAR _ShopWithPreviousDate = 
ADDCOLUMNS(
    VALUES('Date of Rating Report'[Name of Shop ]),
    "@Previous", CALCULATE(MAX('Date of Rating Report'[Date Of Rating]), 'Date of Rating Report'[Latest] <> "Latest")
)
VAR _Result = 
CALCULATE(
    MAX('Summary of Findings'[Rating]),
    TREATAS(_ShopWithPreviousDate, 'Date of Rating Report'[Name of Shop ], 'Date of Rating Report'[Date Of Rating])
)
RETURN 
	_Result

Avg Rating = AVERAGE('Summary of Findings'[Rating])

 

This first chart compares Latest Rating with the average of all previous ratings per shop

PaulOlding_0-1633007493892.png

 

... and this one compares the latest rating with the most recent previous rating.

PaulOlding_1-1633007544487.png

 

 

Message 4 of 4
911 Views

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All