topic FILTER TABLE COLLUMN BY COLUMN VALUE IN RELATED TABLE in DAX Commands and Tips

FILTER TABLE COLLUMN BY COLUMN VALUE IN RELATED TABLE

Anonymous — Thu, 30 Sep 2021 11:55:20 GMT

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:

SOURCE	NAME OF SHOP	Rating
Source 1	Shop 1	5
Source 2	Shop 1	2
Source 3	Shop 2	1
Source 4	Shop 2	2
Source 5	Shop 3	3
Source 6	Shop 3	4
Source 7	Shop 1	5
Source 8	Shop 1	3
Source 9	Shop 2	3
Source 10	Shop 2	6
Source 11	Shop 3	7
Source 12	Shop 3	8
Source 13	Shop 1	4
Source 14	Shop 1	3
Source 15	Shop 2	6

Questy 2 - Date of Rating Report

SOURCE	Name of Shop	Date Of Rating	Latest
Source 1	Shop 1	12/1/2020	Not Latest
Source 2	Shop 1	12/2/2020	Not Latest
Source 3	Shop 2	12/3/2020	Not Latest
Source 4	Shop 2	12/4/2020	Not Latest
Source 5	Shop 3	12/5/2020	Not Latest
Source 6	Shop 3	12/6/2020	Not Latest
Source 7	Shop 1	12/7/2020	Not Latest
Source 8	Shop 1	12/8/2020	Not Latest
Source 9	Shop 2	12/9/2020	Not Latest
Source 10	Shop 2	12/10/2020	Not Latest
Source 11	Shop 3	12/11/2020	Not Latest
Source 12	Shop 3	12/12/2020	Latest
Source 13	Shop 1	12/13/2020	Not Latest
Source 14	Shop 1	12/14/2020	Latest
Source 15	Shop 2	12/15/2020	LAtest

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

Re: FILTER TABLE COLLUMN BY COLUMN VALUE IN RELATED TABLE

PaulOlding — Thu, 30 Sep 2021 12:19:13 GMT

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.

Re: FILTER TABLE COLLUMN BY COLUMN VALUE IN RELATED TABLE

Anonymous — Thu, 30 Sep 2021 12:22:40 GMT

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

Re: FILTER TABLE COLLUMN BY COLUMN VALUE IN RELATED TABLE

PaulOlding — Thu, 30 Sep 2021 13:15:27 GMT

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

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