Filter from Dimension table to have records from Fact table

[prvn]

I have a Database with following Dimensions and Fact table.

CREATE TABLE DimCategory
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name VARCHAR(100) NOT NULL,
	...
)
GO

CREATE TABLE DimSubCategory
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name VARCHAR(100) NOT NULL,
	CategoryID INT FOREIGN KEY REFERENCES DimCategory(ID),
	...
)
GO

CREATE TABLE DimLocation
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name VARCHAR(100) NOT NULL,
	...
)
GO

CREATE TABLE DimDate
	(	[DateKey] INT primary key, 
		[Date] DATETIME,
		...
	)
GO

CREATE TABLE FactConsumed
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name VARCHAR(100) NOT NULL,
	CategoryID INT FOREIGN KEY REFERENCES DimCategory(ID),
	SubCategoryID INT FOREIGN KEY REFERENCES DimSubCategory(ID),
	LocationID INT FOREIGN KEY REFERENCES DimLocation(ID),
	DateID INT FOREIGN KEY REFERENCES DimDate(DateKey),
	...
)
GO

When i add filters from dimension tables Category, SubCategory, Location, Date from dimension tables for the report in Power BI, the filter shows all records that does not exist in Fact table. How can i restrict the filters to show only the records that are in Fact. Example the date range filter should only display date range based on Fact table available date ranges. When Project record is selected in filter, other filters should update to show records related to the selected project that is in the Fact table.

[v-yuta-msft]

@prvn ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@prvn ,

The selection in a slicer chart will show every data in one column. So I'm afraid slicer will show every data in the dimensional table. You can create a slicer based on columns in fact table instead.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.