Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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]))
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.
@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
You can mark an imported table from DB a Date table o take advantage of time intelligence
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?
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
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]))
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 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
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
To learn more about Power BI, follow me on Twitter or subscribe 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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |