Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 

need Help with measure
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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.

     

     

3 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 

      • PaulOlding's avatar
        PaulOlding
        Icon for Solution Sage rankSolution Sage
        4 years ago

        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.