I’m new to Power BI and creating measures with DAX. I have two tables (‘Visits’ and ‘Customers’). I want to get a distinct count for the [customer_id] in the Visits table where ‘Visits'[closedFlag] = 1 and ‘Customers'[formFLG] = 1. Basically, I’m trying to create a measure that counts the customer id based on two conditions from two different tables. There is an one to many relationship between Customers and Visits. This is the DAX measure I tried to create…
= CALCULATE(DISTINCTCOUNT(‘Visits'[CustomerID]),’Visits’,’Visits'[closedFLG] = 1 && ‘Customers'[formFLG] = 1)
But I get the following error…
"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.
Thanks for any help with this I can receive.
>Solution :
You are using the CALCULATE() wrong here.
You don’t need to specifically name the table ‘visits’ after your DISTINCTCOUNT() and you can not use the && operator when looking at two different tables. (Mind that this is possible if you would be looking at two columns from the ‘Visits’ table.)
This is easy to fix by getting rid of some of your syntax or by using the FILTER() syntax, which would result in the following two solutions:
[MeasureName] = CALCULATE(
DISTINCTCOUNT('Visits'[CustomerID]),
'Visits'[closedFLG] = 1,
'Customers'[formFLG] = 1)
[MeasureName] = CALCULATE(
DISTINCTCOUNT('Visits'[CustomerID]),
FILTER('Visits','Visits'[closedFLG] = 1),
FILTER('Customers', 'Customers'[formFLG] = 1))