DAX Query : Calculate a measure and apply a filter condition based on a column from another table

Doshi, Siddhi 21 Reputation points
2021-08-23T15:02:12.02+00:00

We have two tables with many : 1 relation between Table1 and Table2.
Measure Formula:

Measure1 =
CALCULATE(DISTINCTCOUNT(Table1[Form ID]),
Table1[Post Visid High Low] <> "10"
&& Table1[Form Completions] = "1"
&& Table1[hit Source] = "1"
&&Table1[Exclude Hit] = "0"
&& Table1[Web Activity Date] <= RELATED(Table2[Lead Created Date]))

This is giving error for the related table.
The relationship is established correctly and works fine for the derive columns.

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
439 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
{count} votes

Accepted answer
  1. Darren Gosbell 2,376 Reputation points
    2021-09-06T22:42:25.557+00:00

    Without the text of the error message or any details of the direction and cardinality of the relationship we can really only guess at what might be wrong here.

    If Table2 is on the many side of the relationship then RELATED() will not work as that there will potentially be many rows returned. One option in this case is to get aggregate the value and get the max or min of the [Lead Created Date].

    eg

    Measure1 =
    var _maxLeadCreatedDate = MAX(Table2[Lead Created Date])
    return CALCULATE(DISTINCTCOUNT(Table1[Form ID]),
    Table1[Post Visid High Low] <> "10"
    && Table1[Form Completions] = "1"
    && Table1[hit Source] = "1"
    &&Table1[Exclude Hit] = "0"
    && Table1[Web Activity Date] <= _maxLeadCreatedDate)


0 additional answers

Sort by: Most helpful