Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AvPowerBI
Post Patron
Post Patron

Only show results in Filter Pane where they Match

Hi,

 

How do I make the fields that are in my Filter Pane only show results that match in the Fact Table? For example..

 

I have Three tables

 

SalesFact

DimCustomer

DimCalendar

 

The table SalesFact consists of fields like OrderNo, OrderLineNo, Product, Value, CustomerKey, InvoiceDateKey and has about 10 orders on there.

 

The table DimCustomer consists of fields like CustomerKey, CustomerName, ContactName and joins to the table SalesFact by CustomerKey to CustomerKey  and has about 100 Distinct Customer Names.

 

The table DimCalendar consists of fields like DateKey, Date, WeekDay, Month, Year and joins to the table SalesFact by DateKey to InvoiceDateKey and has about 10,000 records showing per day for a Date between 28-Feb-1997 to 17-May-2025.

 

I have added the field Year to the Filters  pane but I am seeing the Year values range between 1997 to 2025 and the values I only wish to see is 2019 and 2020, this is because the table SalesFact only has Invoiced Orders for Years between 2019 and 2020.

 

How do I make it just show these values, I want it to do somthing like and Inner Join byut do not get this option in the Data Model. I could amend the SQL Query in Power Query to do an exisits in each Dimension but there must be another way?

 

Thanks

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi @AvPowerBI , 

The relationship can't affectthe field in filte pane, so it will show all values in [CustomerName] field. If you want to show it based on Sale Fact table, you could try to filter it in M code to modify values in  [CustomerName]. You could add a step(right click "Changed Type", Insert Step After ) in  DimCustomer Table like below

= Table.SelectRows(#"Changed Type", each List.Contains(#"SalesFact"[CustomerKey], [CustomerKey]))

 769.PNG770.PNG

Best Regards,
Zoe Zhi

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

 

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@AvPowerBI , you can pass query parameter

https://community.powerbi.com/t5/Desktop/parameterize-connection/m-p/205900#M90712

 

Or when you create a connection, you have an Advance option, there you can write down a query

AdvanceProperty.png

 

You can mark an imported table from DB a Date table o take advantage of time intelligence

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

 

Hi amitchandak,

 

Thanks for you reply, the Advanced Option you have mentioned I currently have my SQL Query in there

i.e. 

SELECT *

FROM DimCalendar 

 

what is different from me amending the SQL to do something like this ...

 

SELECT *

From DimCalendar c

WHERE EXISTS ( SELECT 1 FROM SalesFact s WHERE c.DateKey = s.InvoiceDateKey )

 

Where you have suggested on using the Query Parameter?

 

And would I have to do this on all the Dimensions I incorporate just to do a WHERE exists if it shows in the SalesFact table?

 

dax
Community Support
Community Support

Hi @AvPowerBI , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will test this in my environment

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Hi @dax 

 

Thanks for the reply.

I have attached the at the below 

 

https://1drv.ms/u/s!Aknl2UdxdHn0dsjIPuxs1UaYdfI?e=2n8y8v 

 

You will see on the Filter Pane it is showing all the Customer Names, I only want the Filter Pane to show Customer Names where they have made a Sale. So it shouldn't show the following customers:


Qlik
McDonalds
Kentucky
Amazon
Lacoste

 

As the End Users are filtering on one of these Customers and then seeing no Data and they think something is wrong with the report.

 

Thanks

dax
Community Support
Community Support

Hi @AvPowerBI , 

The relationship can't affectthe field in filte pane, so it will show all values in [CustomerName] field. If you want to show it based on Sale Fact table, you could try to filter it in M code to modify values in  [CustomerName]. You could add a step(right click "Changed Type", Insert Step After ) in  DimCustomer Table like below

= Table.SelectRows(#"Changed Type", each List.Contains(#"SalesFact"[CustomerKey], [CustomerKey]))

 769.PNG770.PNG

Best Regards,
Zoe Zhi

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

 

rubinboer
Resolver II
Resolver II

hi there you can create a new date table based on the first and last date in your sales table.

Sales Date =

Var start_date = FIRSTDATE(SalesDateColumn)

Var last_date = LASTDATE(SalesDateColumn)

RETURN CALENDER(start_date, last_date)

 

using this table for the slider will only show the dates where you had those invoices

mahoneypat
Employee
Employee

You can make a Calendar table that automatically adapts to the min/max of your fact table, using DAX or in the query editor.  Please see this post on how to do it in DAX.

 

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi,

 

Thanks for your reply, the link you have provided is a Calendar created on the fly using DAX but I want to use the Calendar I have from my source (SQL Server) the only reason is that it has custom flag fields like IsToady, IsYesterday, IsCurrentMonth, IsCurrentYear etc..

 

I also have the same Issue with the field Customer Name on the Filter pane, it is showing all 100 Distinct Customer Names but in my SalesFact table there is only 5 Customer Names that have made an Invoice, so it is confusing the End User when hey select a Customer Name from the filter pane but they haven't actually made a Sale.

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.