CODEWITHSUNDEEP
powerbidax
vanHohenheim
vanHohenheim

Reputation: 127

Filter dim based on fact table

I am wondering is it possible to filter dim table based on fact table? What I mean by that is that I have a dim table with records that are not all avalible in fact table. I load following dummy data to Power BI and create relationship by segment.

date customer segment value
01.01.2021 1 A 10
02.01.2021 1 A 10
03.01.2021 1 A 10
04.01.2021 1 A 10
01.01.2021 2 B 20
02.01.2021 2 B 30
03.01.2021 2 B 40

dict table:

segment segment_desc
A Name of A
B Name of B
C Name of D
D Name of D

Now I create one table with all column from facts and slicer with dim table. enter image description here

As we see dim slicer shows all records from dim even if there are no coresponding records in fact table. I recall that for instance in QlikView we would only see records that are also in fact table. Is it possible in Power BI? Enabling bi directional relationship doesn't do the trick - table is still not filtering the Slicer. I know that I can do inner join with fact table, but maybe there is a way to avoid this cumbersome solution.

Upvotes: 2

Views: 3776

Answers (1)

codyho
codyho

Reputation: 280

This is one of the most annoying features of Power BI if you ask me. But there is a solution!

You need to write a new measure.

Dim Slicer Filter = INT( NOT ISEMPTY( 'fact table name' ) )

Next, make sure the dim slicer is selected, then drag and drop the new measure to the Filter pane. Drag the measure to one of the Filters on this visual boxes. In the options, make sure "is" is selected from the Show items when the value dropdown and then type in 1 in the input box below. Should be good to go after hitting "Apply filter."

If you need more information on this functionality. SQLBI has an article on this very subject here.

Upvotes: 4

Related Questions