Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Solution Sage
Solution Sage

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors