cancel
Showing results for
Did you mean:
Helper I

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

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!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User IV

@YanaBykovskaya , 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))

Proud to be a Super User!

Helper I

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

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Thank you very much!

Announcements