topic Re: Filtering using "in" between two linked tables in Dax in DAX Commands and Tips

Filtering using "in" between two linked tables in Dax

atahancelik — Fri, 10 Nov 2023 00:19:34 GMT

I have two tables named qoutes and sales and these two tables are associated with customerid. I want to count the records in my Qoutes table by grouping them by month and day, but while doing this, I want to count only the values of customer IDs with GP > 0 in the sales table. In other words, I want to filter the customerid values returned from another table by using "in" in a calculate query in which I use the Countrows function. Thanks in advance 🙂

CALCULATE (
    COUNTROWS('Qoutes'),
    RELATEDTABLE('Sales'),
    'Sales'[GP] > 0,
    'Sales'[customerid] IN VALUES('Qoutes'[customerid]),
    VALUES('Qoutes'[Month]),
    VALUES('Qoutes'[Day])
)

There are 15 customerids in the Sales Table in the given date range, but there are 10 customerids with Gp > 0. I want to count the values of these 10 customerids in my Qoutes table.

Re: Filtering using "in" between two linked tables in Dax

Greg_Deckler — Fri, 10 Nov 2023 00:58:51 GMT

atahancelik Sample data would help make this clearer. Put Month and Day into a visual as an adhoc hierarchy. Then maybe a measure like this:

Measure = VAR __Customers = DISTINCT(SELECTCOLUMNS(FILTER('Sales', [GP] > 0), "__CustID", [customerid])) VAR __Result = COUNTROWS(FILTER('Quotes', [customerid] IN __Customers)) RETURN __Result

Re: Filtering using "in" between two linked tables in Dax

shop-tinh-yeu — Fri, 10 Nov 2023 18:36:21 GMT

thank you