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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors