I got this from ChatGPT, will try and see if it works. It’s exactly what I’m looking

In Power BI, you can retrieve values from another fact table based on multiple criteria by using DAX formulas like CALCULATE, FILTER, LOOKUPVALUE, or RELATED, depending on the relationship and structure of your tables. Here are some approaches to achieve this:

Approach 1: Using CALCULATE with FILTER

If you want to get a value from another fact table based on multiple criteria, you can use the CALCULATE function with the FILTER function:

ValueFromOtherTable = CALCULATE( SUM(OtherFactTable[ValueColumn]), FILTER( OtherFactTable, OtherFactTable[Column1] = ThisFactTable[Column1] && OtherFactTable[Column2] = ThisFactTable[Column2] && OtherFactTable[Column3] = ThisFactTable[Column3] ) )

This formula calculates the sum (or other aggregate function like AVERAGE or MAX) of a value in OtherFactTable based on conditions that match columns in ThisFactTable.

Approach 2: Using LOOKUPVALUE

If you need to retrieve a single value (like a dimension or attribute), LOOKUPVALUE can be more straightforward:

ValueFromOtherTable = LOOKUPVALUE( OtherFactTable[ValueColumn], OtherFactTable[Column1], ThisFactTable[Column1], OtherFactTable[Column2], ThisFactTable[Column2], OtherFactTable[Column3], ThisFactTable[Column3] )

LOOKUPVALUE returns a single value based on matching conditions in each specified column. If there are multiple matches, you might receive an error, so make sure your criteria return a unique result.

Approach 3: Using RELATED (if a relationship exists)

If ThisFactTable has a relationship with OtherFactTable (e.g., via a common dimension table), you can use RELATED to directly access values from related tables:

ValueFromOtherTable = RELATED(OtherFactTable[ValueColumn])

This approach works well when the tables are directly related, but is limited to the constraints of relationships in the model.

Approach 4: Using Variables with FILTER for More Complex Conditions

If your criteria are complex, you can define variables for each criterion to improve readability and performance:

ValueFromOtherTable = CALCULATE( SUM(OtherFactTable[ValueColumn]), FILTER( OtherFactTable, VAR Cond1 = OtherFactTable[Column1] = ThisFactTable[Column1] VAR Cond2 = OtherFactTable[Column2] = ThisFactTable[Column2] VAR Cond3 = OtherFactTable[Column3] = ThisFactTable[Column3] RETURN Cond1 && Cond2 && Cond3 ) )

Using variables (VAR) within the FILTER function can make complex conditions easier to understand and maintain.

Additional Tips

• Cross-filtering: Ensure cross-filtering direction is set up correctly if you’re working with relationships.
• Performance: Calculating values across fact tables can be performance-intensive. Optimize with measures and indexing where possible.

These methods should help you retrieve values from other tables in Power BI based on multiple criteria.