slicer not correctly displaying slowly changing dimension

[lprince]

I am using a SSAS cube as my data source and have a date slicer and a sales group geographic type slicer based on a slowly changing dimension (sales groups definitions (which states are covered by which sales group) change over time) to filter fact data.  I would have thought the slicer would display different information depending on what date range was selected on the date slicer. I have the interaction set for the date slicer to filter the sales slicer, but I can't seem to make it work correctly.  Some states appear under multiple sales groups.  The data displays correctly (no counts when there aren't supposed to be any), but the slicer is misleading.  Is there any way to hide the invalid selections?

[dkay84_PowerBI]

This problem likely results from your data model.  The "invalid" selections are a result of how the relationships are set up between the tables, so you need to see how your model is set up and think about why states are appearing under multiple groups. If you want to share an annonymized pbix or some sample data, we can help you with the correct data modeling.

[lprince]

a simplifed version of the model is like this:

DateDIM:

DisplayDate, DateYear....

SalesDIM:

SurrogateID, SalesGroupID, State, DateStart, DateEnd

FactTable:

FactID, ReportDate, SurrogateID,CountX

The relationship between the SalesDim and the FactTable is set on SurrogateID, the relationship between the DateDim and the FactTable is set on ReportDate.  The SurrogateID key in the fact table is updated by an ETL job depending on whether or not the ReportDate is between the DateStart and DateEnd, so I get what you're saying.  When I query my cube natively, I use a non empty filter to hide the columns with null data.  I have another visualization tool where this is the default option for slicers, so I was hoping I could find a way to get power bi to work this way also.

[dkay84_PowerBI]

For blank values, you can set a visual/page/report level filter and deselect the blank value.  However, I'm not following how this relates to your original issue: "Some states appear under multiple sales groups"

---

[lprince]

ok, let me try it this way:

SalesGroup1 has TX, OK as the sales district from 1990-1995.  Growth happens, reorganization etc. 

SalesGroup1 gets redefined as TX only effective 1/1/1996 and we create a SalesGroup2 to cover OK from 1996-infinity. 

So the SalesDim table would look like:

SurrogateID, SalesID, State, StartDate, EndDate

1                     SG1     TX       1/1/1990 12/31/9999

2                     SG1     OK      1/1/1990 12/31/1995

3                     SG2     OK      1/1/1996 12/31/9999

Fact Table:

FactID, ReportDate, SurrogateID, CountX

1           6/1/1991       1                  100

2           6/1/1991       2                  50

3           6/1/2000       1                  150

4           6/1/2000       3                  75      

If I am reporting on sales happening during 1991, I would only want the slicer to display TX, OK for SG1 and SG2 shouldn't exist.  If I am reporting on sales happening in 2000, I would want the slicer to display only TX for SG1 and put OK under SG2.  Instead, the slicer displays OK under both SG1 and SG2 regardless of my date selection.  If I cross the date barrier (like a complete history for all years) for my date selection,  I would expect to see OK in both SG1 and SG2.  Does that explain it better?

[dkay84_PowerBI]

Yes thanks for the details.  Are you able to share your pbix?  I am still confused as to the origin of your Fact table.  Is this a calculated table?  Are you using a date dimension table?

[lprince]

I haven't forgotten about this. I've been swamped. But I realized I need to ask a different question. After pondering it for a bit, I can understand why Power BI is including all the selections in the Sales Dim.  It's just reading the Dim table as the source of the slicer, so all the SalesGroups/State combinations are valid regardless of date interval.  If I filter by sales group before I filter for date, it would need to have the state under both sales groups.  My joins are working because the count is correct.  I'm just not happy with the fact the person running the report is lead to belive the state is contributing to the count because they can see it in the slicer.

So perhaps the better question would be as follows.  On SQL server, I can run the following MDX:  

select non empty( [Measures].[CountX]) on columns,

         non empty([SG].[Salesgroup].[Salesgroup],[SG].[State].[State]) on rows

     from MyCube

    where [ReportDates].[ReportYear].&[2017])

The non empty function causes all the null values on both the rows and columns to disappear and I only get the valid SalesGroup/State combinations for that time frame that have actual counts.  Is there a way to get the slicer to hide states with a count of NULL or 0?

[lprince]

My pbix file doesn't have any data in it, I can probably mock one up tomorrow.  My current one is a direct query into a SQL Server 2012 SSAS cube, so there's no data or model to look at inside Power BI, the Dim tables and Fact table just show up on the right side in Power BI under fields.  Here's a simple picture of what the model would look like in the DWH, please excuse my drawing skills.  The DateDim.DisplayDate->Fact.ReportDate is a 1 to Many and the SalesDim.SurrogateID->Fact.SurrogateID is a 1 to Many. 

Fundamentally, my fact table has individual records for lots of locations and I'm summing the count based on multiple slicer selections.  I'm just trying to keep my example simple. In my pbix file, I have a date slicer (source DateDIM.DisplayDate)  and a custom hierarchy slicer (source SalesDim.SalesGroupID, SalesDim.State) and a chart to display totals.  I tried changing the hierarchy slicer into two separate slicers, one with a source of SalesDim.SalesGroupID and one with a source SalesDim.State, but it still displayed the state for each SalesGroup.  I'll mock something up tomorrow with data I can put into a pbix as an example. Thanks!