Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have the following query that is returning all categories when it should only return category "Audio". I have 3 filters on the CALCULATE and they all work except 'Product Category'[Category] = "Audio"
EVALUATE
VAR SelectedYears =
CALCULATE(
DISTINCTCOUNT('Date'[Calendar Year]),
CROSSFILTER(Receipts[SaleDateKey], 'Date'[DateKey], BOTH),
DATESBETWEEN(Receipts[Sale Date], DATE(2007, 1, 1), DATE(2009, 12, 31)),
ALLSELECTED()
)
VAR StatusGranularity =
SUMMARIZE (
Receipts,
Store[Store Name],
'Product Category'[Category]
)
VAR OpenStores =
FILTER (
StatusGranularity,
VAR YearsWithSales =
CALCULATE(
DISTINCTCOUNT('Date'[Calendar Year]),
store[CountryRegion] = "Germany",
'Product Category'[Category] = "Audio",
store[Store Type] = "Store",
CROSSFILTER(Receipts[SaleDateKey], 'Date'[DateKey], BOTH),
ALLSELECTED('Date'[Calendar Year])
)
RETURN
YearsWithSales = SelectedYears
)
RETURN
OpenStores
This is the result
https://drive.google.com/file/d/1hyUnvnjfDDqjZ7zQykD2Ij6vtBcH2KvM/view?usp=drive_link
Solved! Go to Solution.
pls try this
EVALUATE
VAR SelectedYears =
CALCULATE(
DISTINCTCOUNT('Date'[Calendar Year]),
CROSSFILTER(Receipts[SaleDateKey], 'Date'[DateKey], BOTH),
DATESBETWEEN(Receipts[Sale Date], DATE(2007, 1, 1), DATE(2009, 12, 31)),
ALLSELECTED()
)
VAR StatusGranularity =
FILTER(SUMMARIZE (ALL(
Receipts),
Store[Store Name],
'Product Category'[Category]
),'Product Category'[Category] = "Audio")
VAR OpenStores =
FILTER (
StatusGranularity,
VAR YearsWithSales =
CALCULATE(
DISTINCTCOUNT('Date'[Calendar Year]),
store[CountryRegion] = "Germany",
store[Store Type] = "Store",
CROSSFILTER(Receipts[SaleDateKey], 'Date'[DateKey], BOTH),
ALLSELECTED('Date'[Calendar Year])
)
That should work now
pls try this
EVALUATE
VAR SelectedYears =
CALCULATE(
DISTINCTCOUNT('Date'[Calendar Year]),
CROSSFILTER(Receipts[SaleDateKey], 'Date'[DateKey], BOTH),
DATESBETWEEN(Receipts[Sale Date], DATE(2007, 1, 1), DATE(2009, 12, 31)),
ALLSELECTED()
)
VAR StatusGranularity =
FILTER(SUMMARIZE (ALL(
Receipts),
Store[Store Name],
'Product Category'[Category]
),'Product Category'[Category] = "Audio")
VAR OpenStores =
FILTER (
StatusGranularity,
VAR YearsWithSales =
CALCULATE(
DISTINCTCOUNT('Date'[Calendar Year]),
store[CountryRegion] = "Germany",
store[Store Type] = "Store",
CROSSFILTER(Receipts[SaleDateKey], 'Date'[DateKey], BOTH),
ALLSELECTED('Date'[Calendar Year])
)
Thanks,
I still dont understand why I cant filter StatusGranularity on 'Product Category'[Category] = "Audio". StatusGranularity should preserve data lineage on on 'Product Category'[Category], The other filters for Columns from Store work.
No access to file
User | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
66 |
User | Count |
---|---|
115 | |
103 | |
93 | |
64 | |
61 |