Filter two tables on Multiple Criteria
Hello! this might be confusing and I can't share any data so I will do my best! I have two tables: SynthesizedItems and Failures. I have a report page dedicated to fail rate. This is just the number of fails/number of synthesized items. (measures in the failure table) Originally, I linked the two tables to a common date table. this allowed me to see the fail rate adjusted based on the users filtering by date. Then, I realized I needed to be able to break this down further. I added an additional table called DepartmentFilter and linked it to department in both the fail and synthesis tables. this seems to work. Now my question. My report has SEVERAL factors involved that users can slice their data by (all coming from the failure table) and I noticed that if the user filters to a certain customer, the fail table will filter but not the synthesized items table. (makes sense because they are not related, although customer exists as a field in both tables). Is it possible to make this method work where any filter the user applies will affect BOTH tables and therefore give an accurate rate? Is there a limit to how many common "filter" tables I can create and link to both? Is there a way to do it other than creating tables for each field? really trying to avoid this since it would be about 10 fields.
Relationship issues showing BLANK values
Hi All, I built a quick model with a lot of rows (10 million). The model analyses the YOY variances per product product group. A product reference is linked to a product group. In some cases, the data might have mistakes and 1 reference is linked to multiple product groups. For some reasons, I can see some "(blank)" values and I don't understand why. When I click on the revenue, it shows nothing as if it is empty. Could you please help? Thanks so much for your assistance!
DAX code filtering tabled based on selected value (show other values)
Hi all, I have an issues to which I cannot find the solution. I hope someone can help me out. I've created an example dataset to explain the problem: customers order food and drinks in a restaurant. They can order multiple types of food and drinks during 1 visit. Here is a sample of 5 different customers with their orders: I can edit this table to make it a one record per customer list: Now what I would like to know is when I select one item in a slicer/filter, what are the other most ordered items per type (food/drinks) based the customers that ordered the selected item? Example: I select as item Fries. Customer B, C and E ordered Fries. During the same order, they also ordered other food and drink items. I want to count the number of times the other items were ordered to see which item is most often ordered together with fries. This is what I would like to see then: My thoughts were to first create new DIM table with the type and item combinations (a food and drinks list) which can then be used for the filter. I can create a relationship between the new DIM table and the FACT table in the model view so that I can filter the customers with orders that contain the selected item. Then I somehow need to use this list of selected customers to filter another table (a copy of the original FACT table with all the customers and their order information?) so that I can see the whole order of these customers. And then I can create a visual of the number of items, per type, in which I exclude the selected item. I hope someone can help me out 🙂 Many thanks, Anne
How to use columns as filters from different tables for measures
Hello! I have created a measures table (measures_counts_by_date) which has counts for number of occurences based on the date from another table (litigations). It works in reports when I am using columns only from the litigations table. However, I would like to use columns from other tables such as sightings , producers etc. for my reports. As I have seen this does not work with the measure. The measure is calculated in the following way: closed_count = CALCULATE(COUNT(litigations[id]), USERELATIONSHIP(dates[Date],litigations[closed_at])) Should I somehow create a relationship between my measures table and the other tables in order to be able to use columns from other tables? I will be happy if you share the best practice in this scenario. Many thanks!
PowerBI dataset simple relationship issue
Hello guys, I am unable to understand why I cannot create visuals with data from my two fact tables joined through a dimension table. The schema is described below, I joined the two tables through a dimension based on the same id. If I want to create a visual in which I pull attributes from Fact One and Fact Two, i get an error saying there is no valid relationship between the tables. I tried switching the cross filter direction to 'both' for both relationships: did not work. Tried to pull attributes from all three tables, thinking that maybe all three tables have to contribute to the visual for it to work. Same issue. The only way I can link the two tables in the same visual is by doing a many to many relationship between the two fact tables. What am I doing wrong? Also, I have a similar issue with products table where I am linking Fact Two to Product table (fact:many to product:one), then linking Product dimension to Product Options dimension (Product:one to Product Options:many). I cannot split the fact table sales data by Product Options. The only way to make it work is again, doing a many to many relationship between Fact Two and Product Options. Please help!Creating relationship between two tables using null calculated columns SSAS
Hi! I'm having a problem of recreating a solution that my previous colleage did and nobody has no idea what the solution really means. There are two tables in tabular model: a dimension (D) and a fact table (F) which have the same source but each table contains different number of attributes. Each table contains a calculated column "Link" which has no expression: These two tables has a relationship (Many to one, Both Directions) using these two calculated columns: How does it even work? Even though I don't understand the logic, I tried to recreate this solution using another two tables, but I'm getting an error: Link column contains a duplicate value null and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. Can somebody please explain what is going on and why am I getting an error while trying to recreate almost the same thing that actually works? Thank you in advance!
