cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AleksandrMe
Resolver I
Resolver I

FILTER TABLE COLLUMN BY COLUMN VALUE IN RELATED TABLE

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 

1 ACCEPTED SOLUTION

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

3 REPLIES 3
PaulOlding
Super User
Super User

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.

 

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 

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

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors