Forum Discussion

anandfarmers's avatar
anandfarmers
Advocate I
2 years ago

ALLSELECTED() - data modelling issue

Hi All,

 

I have the below schema

 

The DatePeriod can be Yesterday, This Week, This Month, etc. So a Date can be in multiple Date Periods. 

Date Period has Many to One relationship with DimDate.

 

If I use the DimDate date column as slicer and use ALLSELECTED in a measure to select Max Sales for the period, it works well.

 

 

But if I remove the DimDate slicer and use the Date Period as a slicer and select This Week, which in turn should filter DimDate which in turn should filter FactInternetSales.

But in this scenario, the Max Sales is displayed for all the dates in DimDate.

Why is this?

 

 

I can include a IF [_Sales Amount] > 0 check and restrict it to show only the required dates, but I ma trying to understad the ALLSELCTED behaviour in snowflake scema scenario.

 

Here is the measure I am using:

 

 

_Max Sales =
var _SalesSummary =
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(FactInternetSales, DimDate[FullDateAlternateKey])
        ,"@Amt" ,[_Sales Amount]
    )
    ,ALLSELECTED()
)

var _MaxSales =
MAXX(
    _SalesSummary
    ,[@Amt]
)
RETURN
    _MaxSales

 

 

 

I can't SUMMARIZE FactInterSales by DatePeriod table. I am assuming the relationship cannot traverse many to one, eventhough it is set to filter DimDate table. Is this the cause?

How can I solve this scenario in data modelling?

2 Replies

  • Date Period has Many to One relationship with DimDate.

    yes, but the search filter goes both ways, making this rather ineffective.  Cut the link and use measures instead.

    • anandfarmers's avatar
      anandfarmers
      Advocate I

      Thanks lbendlin .

      The users need to be able to filter by Date Period. e.g. This week, this month, etc. So they don't need to enter dates - that is the requirement.

      I cannot use a measure as a Slicer.