Forum Discussion

AlB's avatar
AlB
Community Champion
2 years ago
Solved

Fact table filtering dim table

Hi all, We have a simple model with one dim table (DimT) and one fact table (FactT) (pbix attached) :                          We now create a simple table visual (V1) with DimT[...
Show and Tell
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    AlB 

    The difference lies in the DAX queries Power BI generates for the different visuals.

     

    1. When no measure is included in the visual (as in V2/V3), a "hidden measure" is automatically added to the query within SUMMARIZECOLUMNS, in this case with this expression (wrapped in CALCULATE):

    COUNTROWS ( 'FactT' )

    This ensures that only combinations of Product/Sales values where FacT is nonempty are returned.

    The DAX query also includes a check that at least one of Product/Sales is nonblank.

     

    2. When a measure is included the visual (as in V4), the nonblank values of this measure will determine which combinations of Product/Sales values are returned. In the case of [Product_M], this measure is nonblank for all combinations in the Cartesian product of Product/Sales values.

     

    We could summarise this as: If a table visual includes fact/dim columns but no measures, Power BI will automatically filter the combinations of fact/dim columns to those where fact is nonempty.

     

    I extracted the essential parts of the V2/V3 & V4 queries below to illustrate:

    -- V2/V3 DAX Query
-- Includes rows where
--   1. CALCULATE ( COUNTROWS ( FactT ) ) is nonblank,
--      which is true when FactT is nonempty when Product/Sales values are applied as filters.
--   2. At least one of DimT[Product] or FactT[Sales] is nonblank
EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'DimT'[Product],
        'FactT'[Sales],
        "CountRowsFactT", CALCULATE ( COUNTROWS ( 'FactT' ) )
    ),
    OR ( NOT ( ISBLANK ( 'DimT'[Product] ) ), NOT ( ISBLANK ( 'FactT'[Sales] ) ) )
)

-- V4 DAX Query
-- Includes rows where
--   1. [Product_M] is nonblank.
--      which is true for any combination of DimT[Product] & FactT[Sales]
--      resulting in the Cartesian product.
EVALUATE
SUMMARIZECOLUMNS (
    'DimT'[Product],
    'FactT'[Sales],
    "Product_M", 'FactT'[Product_M]
)

    It would be interesting to understand the general rule determining what expressions are automatically added when a visual includes no measures. Presumably tables on the "end" of a chain of 1:many relationships would always be aggregated.

     

    Regards

AmiraBedh's avatar
AmiraBedh
Super User
2 years ago

In a one-to-many relationship between tables in Power BI, the default filtering behavior is from the "one" side (DimT) to the "many" side (FactT). This means that if you filter or slice based on DimT[Product], only the corresponding rows in FactT that have a match with DimT will be shown. The opposite is not true by default: selecting a value from FactT will not filter DimT unless you have enabled bidirectional filtering (which is generally not recommended as it can cause ambiguous results).


When you design visuals in Power BI, the way the data is presented is based on the order in which you add fields to the Values section of a visual. But this doesn't mean that the order of the fields changes the filtering behavior; it's just a display preference. The relationship and the filtering defined between tables are maintained.
- **V1**: Summarized view showing total sales for each product. The filtering behavior here is straightforward and as expected.

- **V2**: This visual lists sales for each product in separate rows. Since you have FactT[Sales] set to "Don't summarize", it lists each sales value on a separate line, but still respects the relationship, showing which product it relates to.

- **V3**: The behavior is still the same, but you've changed the visual order. It doesn't mean FactT is filtering DimT. It's simply presenting sales and then its corresponding product. Power BI still understands the relationship and shows the matching DimT[Product] for each FactT[Sales].

To clarify, you are NOT getting a Cartesian product in V3. If you were getting a Cartesian product, you'd see every combination of DimT[Product] and FactT[Sales], which you aren’t.


`SELECTEDVALUE` is a DAX function that returns the value of the column for the row when there's only one value. If there are multiple or no values, it returns a blank. When you use this measure in conjunction with a visual that has FactT[Sales], it checks for the product associated with that sale. If there's only one product for a sale (which there should be, based on your data model), it returns it. It doesn’t create a Cartesian product; it just returns the associated product for the sale.

 

FactT isn't filtering DimT in V3. Instead, it's displaying sales and the corresponding product based on the relationship you've established. If it were a full Cartesian product, you'd see every sales value for every product, which is not what you observed.

The `Product_M` measure you introduced using `SELECTEDVALUE(DimT[Product])` isn't forcing a Cartesian product. It's merely returning the associated product for the sale based on the existing relationship between FactT and DimT.

  • AlB's avatar
    AlB
    Community Champion
    2 years ago

    Hi AmiraBedh 

    Thanks very much for you quick response.

    You have not clarified all of the questions, I'm afraid.

    I did not say V3 shows the Cartesian product. Quite to the contrary, I said it should, but it does not.

    V4 is clearly showing the Cartesian product of Sales and Product. Like I said, the screen cap does not show all the results  (only products A to C) but you can see the whole thing in the attached pbix.

    My main question is, why adding the measure causes the change of behavior between V3 and V4?

    Thanks

    TomMartens MFelix   jdbuchanan71   OwenAuger