Calculate measures on the basis of 2 related tables but ignoring this relationship?

[Anonymous]

Hi everyone!

I have 2 tables: one table is a fact table containing dates field and the number of events, another table is a dimension table containing names and dates of creating these names. These tables are related by the field id_n, but I need calculate measure that will calculate the number of names (from table 2) per each date (from table 1) where created_date (from table 2) is greater than or equal to date (from table 1). 

The formula should looks like measure_table_1 = COUNT( IF(table_2[created_date] > = table_1[date], table_2[id_n]) )

Thanks!

[amitchandak]

@Anonymous , Try like

measure_table_1 = calculate(COUNTX(table_2, IF(table_2[created_date] > = max(table_1[date]), table_2[id_n],blank()) ),values(table_2[id_n]), crossfilter(table_2[id_n],table_1[id_n], none))

[Anonymous]

Hi. Thanks for the answer. But is it possible to do calculation without creating a new table?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure as below, please find the details in the attachment.

Count of names = 
CALCULATE (
    DISTINCTCOUNT ( 'Table 2'[name_id] ),
    FILTER (
        ALL('Table 2'),
        'Table 2'[create_date] >= SELECTEDVALUE ( 'Table 1'[date] )
    )
)

Best Regards

[Anonymous]

Thank you very much!