Calculated Measure of DISTINCTCOUNT over filtered tables

Question

I need to create a calculated measure of distinct count of a column, sourced from two tables with filters applied to each. The tables have a relationship to each other & to a Date Dimension (one is Active, other in Inactive)

I have put together a working sample, with the measures I am using already, and showing a desired state measure.

I created a calculated measure using a SUMMARIZECOLUMNS on each table (USING TREATAS to filter, then did a UNION of these, but it did not retain filter context of the date.

Looking for some DAX help please!

Link to Unit Test

Current measures:

Valid Touch Point Contact =
CALCULATE(
    COUNTROWS(OrderContact),
    OrderContact[Direction]="Inbound",
    OrderHeader[Region]<>"IE"
)

Valid Touch Point Header =
CALCULATE(
    DISTINCTCOUNT(OrderHeader[Order Number]),
    USERELATIONSHIP(OrderHeader[Created Date], 'Date'[Date]),
    OrderHeader[Type]="Web",
    OrderHeader[Region]<>"IE"
)

Can you explain a little more what you want your new measure to count exactly? Is it all the orders that get counted is either one of your first two measures? — Alexis Olson
That's correct. It's essentially the DISTINCT COUNT of Order Number across the logic of the two existing measures. — BenOnline

BenOnline BenOnline · Accepted Answer · 2018-10-18T12:53:55

I solved this with the below DAX. Initially I had attempted to use SUMMARIZECOLUMNS to get the UNION between the two, however by replacing with CALCULATETABLE I was able to achieve the desired result.

In logic terms, I took the total by adding the two measures listed in the above, and simply removed the COUNTROWS where the two had an INTERSECT.

Intersect Measure:

  Touch Point Intersect = CALCULATE(
  COUNTROWS(
  INTERSECT(
     CALCULATETABLE(VALUES('OrderHeader'[OrderNumber]),
        FILTER('OrderHeader',OrderHeader[Type]="Web",OrderHeader[Region]<>"IE"),
        USERELATIONSHIP('OrderHeader'[Created Date],'Date'[Date])),
     CALCULATETABLE(VALUES(Emails[OrderNumber]),
        FILTER('OrderHeader',OrderContact[Direction]="Inbound",OrderHeader[Region]<>"IE"),
        FILTER('Emails',OrderContact[Direction]="Inbound")
     )
  )))

The measure I require is thus:

    Valid Touch Point Contact+ Valid Touch Point Header - Touch Point Intersect

Calculated Measure of DISTINCTCOUNT over filtered tables

1 Answers