Secondary date question

[Safety Guy]

I have a transaction table that contains transactions by date with a many-to-one relationship to a calendar table. On said transaction table I also have a column for review date in which is the date the transaction was reviewed. I added a Calculated Column that is a review counter (puts a "1" in the row if a date exists in the review column) and have a measure to total the review counter.

I need a pivot table with number of transactions and the number transactions reviewed each month. Since a transaction review may not be conducted in the same month as the transaction, the numbers will not always be the same.

I thought I could do it with a measure:
Transaction Review:=CALCULATE([Review Total], 'Transactions'[Review Date"))

As with most of my plans, things aren't working out the way I want. I'm getting the same numbers for each.

Any idea where I went wrong?

Thanks,
Safety Guy

 

[gazpage]

If I understand correctly, you have two date columns in your transaction table.

You need to create two relationships, one to the transaction date column and one to the review date column. One of the relationships will need to be inactive, might as well be the review one.

Putting the transaction date in the part in the pivot table should be straightforward. For the other, google USERELATIONSHIP in DAX. See if you can drop follow that, and maybe post back if any problems.

Gary

 

[Safety Guy]

You do understand correctly Gary. I had a relationship to the review date column, but since it was inactive, I didn't see the purpose in keeping it. I'll look into USERELATIONSHIP and let you know.

Thanks,
Safety Guy

 

[Safety Guy]

Gary...You are quite possibly my new hero! Go ahead and strike the Superman pose and let your cape wave in the air.

Your suggestion worked perfectly. Thanks a bunch!


Thanks,
Safety Guy

 

[Safety Guy]

Issue is solved thanks to gazpage, but I do have another question. Is there a better way I could have asked my original question? Could I have phrased things differently of a better way to explain my tables and relationship(s)? I'm asking for future questions I will have, as I'm sure I will have more, and want to be as clear as possible.

Thanks in advance,
Safety Guy