Need a measure to work out most current date and subtract that from a DAX Measure

[Anonymous]

Hi Experts

I have two tables as shown below (made up data)  Table B is the FACT table and Table A has Complaint Dates and ID's. The two tables are linked via the ID column.

Here is the scenario. If there is a ID match between Table B and A then

1. Find the most current date for ID 1 its 26/09/2020 and for ID 4 its 16/09/2020. Then i want to be able to subtract this from the following dAX measure

DDM Activated = CALCULATE(

COUNTROWS('Table B),

'Table'[Dept] in {"A","B","C"})

Table A

ID    Ref     Date

1      aaa    16.09.2020

1      sds     26.09.2020

2      sds     01.09.2020

3      dfs      04.09.2020

4      dsf      04.09.2020

4      bbb     16.09.2020

Table B

ID    Ref     DEPT

1      aaa    A

5      sds    A

6      dfs     B

4      dsf     C

Relationship Between Table A and B is many to many based on ID column (in real tables)

Currently this measure gives me a count of 4 but i want to show 2 as i have two id matchs from table a for Id 1 and 4 as the customer has logged a complaint and i want to exclude this from final result.

[Greg_Deckler]

@Anonymous First, I would avoid many-to-many relationships. You should create a bridge table of unique values for ID and put that between the two tables.

Second, I do not understand the finding of the last date thing as I do not understand why that matters in the calculation you are proposing.

Third, you don't need CALCULATE. 

Try this:

DDM Activated = 
  COUNTROWS(FILTER('Table (28a)','Table (28a)'[Dept] IN {"A","B","C"})) - 
  COUNTROWS(FILTER('Table (28a)','Table (28a)'[ID] IN SELECTCOLUMNS('Table (28)',"ID",[ID])))

PBIX is attached below sig.

[Anonymous]

Thanks Greg the gods have spoken ill listen. Always appreciate the feedback. Let me test.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure as below:

DDM Activated =
CALCULATE (
    DISTINCTCOUNT ( 'B'[ID] ),
    FILTER ( 'B', 'B'[ID] IN VALUES ( 'A'[ID] ) && 'B'[DEPT] IN { "A", "B", "C" } )
)

Best Regards

Rena