Using DAX, how do I calculate a table when filtering on a related table?

Question

I'm trying to find the intersect of two result sets using DAX, but I'm really struggling to get the two result sets calculated.

I have a fact table, FactCheckForUpdates, that has a relationship to a date table called 'Log Date'. FactCheckForUpdates contains Machine IDs, and I want to return the IDs for the last 2 complete months.

I can calculate the distinct count of Machine IDs using this formula:

2Month Distinct Machines:=CALCULATE (
    [Distinct Machine Ids], 
    FILTER( 
        ALL( 'Log Date' ), 
        ( 'Log Date'[YearMonthNumber] >= MAX( 'Log Date'[YearMonthNumber] ) - 3 ) 
        && ( 'Log Date'[YearMonthNumber] <= MAX( 'Log Date'[YearMonthNumber] ) - 1 )
    )
)

Where 'Distinct Machine Ids' is calculated as:

:=DISTINCTCOUNT([MachineId])

and where 'YearMonthNumber' is calculated on the 'Log Date' table as:

=('Log Date'[YearKey] - MIN('Log Date'[YearKey])) * 12 + 'Log Date'[MonthOfYearKey]

(effectively this gives the number of the month in the context of the entire date dimension).

Can anyone help me update the [2Month Distinct Machines] expression so that instead of returning the distinct count of Machine IDs in the period, it returns a table of the machine IDs?

I've tried using the CALCULATETABLE function, but it won't accept the MAX aggregate on the date filter. The closest I've gotten is this formula:

CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( FactCheckForUpdates, FactCheckForUpdates[MachineId] ),
        "meh", CALCULATE ( SUM ( FactCheckForUpdates[CFUPing] ) )
    ),
    FactCheckForUpdates[LogDateKey] > DATE ( 2016, 4, 1 )
)

but I'm not sure how to use the 'Log Date' table here.

Any help massively appreciated!

score 0 · Accepted Answer · answered Apr 13 '16 at 01:33

0

How about this:

CALCULATETABLE (
    VALUES(FactCheckForUpdates[MachineId]), 
    FILTER( 
        ALL( 'Log Date' ), 
        ( 'Log Date'[YearMonthNumber] >= MAX( 'Log Date'[YearMonthNumber] ) - 3 ) 
        && ( 'Log Date'[YearMonthNumber] <= MAX( 'Log Date'[YearMonthNumber] ) - 1 )
    )
)

Where are you calling this DAX expression from? It's only going to work inside another expression or as part of a query.

answered Apr 13 '16 at 01:33

GregGalloway

11,355
3
16
47

Thanks Greg - I've tried your suggestion, but it returns 0 rows. I think that is possibly due to the CALCULATETABLE function ignoring context. I'll use the expression inside another, basically I want to get two tables of machine IDs, then find the intersect of them and return the row count of intersecting values. Simplified form would be something like: `CALCULATE ( COUNTROWS ( INTERSECT ( , ) ) )` – Jonathan Harding Apr 13 '16 at 07:25
Actually, trying your suggestion outside of DaxStudio and in my model did actually work - my bad! – Jonathan Harding Apr 13 '16 at 11:46

Using DAX, how do I calculate a table when filtering on a related table?

1 Answers1