Why is this DAX query returning all product categories ?

[Martin_R]

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

[Martin_R]

That should work now

[Ahmedx · Accepted 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])
        )

[Martin_R]

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.

[Ahmedx]

No access to file