Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
... and this one compares the latest rating with the most recent previous rating.
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
... and this one compares the latest rating with the most recent previous rating.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |