Skip to content
TheChatArchiveThe Chat Archive
Mistralmistral-medium-2505· Mistral AIHugging Face LMArena Human Preference 140K

How to filter each table by not directly related one

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Metallama-4-maverick-17b-128e-instruct’s answer.
  1. User

    How to filter each table by not directly related one

  2. Mistral Assistant

    Filtering Tables Without Direct Relationships in DAX

    When you need to filter one table based on another table that isn't directly related in your data model, you have several options in DAX. Here are the most common approaches:

    1. Using TREATAS Function

    The TREATAS function is the most straightforward way to create virtual relationships between tables that don't have a physical relationship.

    FilteredTable =
    CALCULATETABLE(
        TableToFilter,
        TREATAS(
            VALUES(FilterTable[FilterColumn]),
            TableToFilter[MatchingColumn]
        )
    )
    

    Example:

    FilteredProducts =
    CALCULATETABLE(
        Products,
        TREATAS(
            VALUES(Stores[Region]),
            Products[ProductRegion]
        )
    )
    

    2. Using INTERSECT with FILTER

    FilteredTable =
    FILTER(
        TableToFilter,
        TableToFilter[Column] IN VALUES(FilterTable[FilterColumn])
    )
    

    Example:

    FilteredCustomers =
    FILTER(
        Customers,
        Customers[CustomerID] IN VALUES(HighValueCustomers[CustomerID])
    )
    

    3. Using LOOKUPVALUE (for single value lookups)

    FilteredTable =
    FILTER(
        TableToFilter,
        TableToFilter[Column] = LOOKUPVALUE(
            FilterTable[FilterColumn],
            FilterTable[KeyColumn], TableToFilter[KeyColumn]
        )
    )
    

    4. Using SUMMARIZE with FILTER

    FilteredTable =
    SUMMARIZE(
        FILTER(
            TableToFilter,
            CONTAINS(
                FilterTable,
                FilterTable[FilterColumn],
                TableToFilter[MatchingColumn]
            )
        ),
        TableToFilter[Column1],
        TableToFilter[Column2]
    )
    

    5. Using Variables for Complex Filtering

    FilteredResults =
    VAR FilterValues = VALUES(FilterTable[FilterColumn])
    RETURN
    FILTER(
        TableToFilter,
        TableToFilter[MatchingColumn] IN FilterValues
    )
    

    Practical Example: Filtering Products by Store Regions Without Direct Relationship

    ProductsFilteredByStoreRegion =
    VAR SelectedRegions = VALUES(Stores[Region])
    RETURN
    FILTER(
        Products,
        Products[ProductRegion] IN SelectedRegions
    )
    

    Important Considerations:

    1. Performance: These methods can be resource-intensive with large datasets. Test performance with your actual data volume.

    2. Context: Remember that these calculations are context-sensitive and will respond to filters in your report.

    3. Alternatives: If you frequently need this filtering, consider creating a physical relationship in your data model if possible.

    4. Bidirectional Filtering: If you enable bidirectional filtering on existing relationships, be cautious as it can lead to ambiguous filter paths and performance issues.

    Would you like me to elaborate on any specific approach or provide an example tailored to your particular data model?

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.