Filter two related tables and the lookup count based on third date table(Triangle Relation)

[Harshalb]

Hello Guys,

Need Help

As shown, I have 3 tables 1.Dim_Date 2.Users 3Transactions

Users is related with Dim_Date on UserCreated Date

Transactions related with Dim_date on transaction date

I need PerUser Number Of transactions

But the interestig thing here is,

If I tick any year from slicer, the Users should get filtered on created date and accordingly the transactions made by users should get filtered based on transaction date.

ex User      createddate

       1               2017

       2               2016

    transactionid   userid     transactiondate

            1                  1               2016

            2                  1               2017

            3                  2               2017

Result if i select 2017 from slicer

           user             count of transactions

              1                      1

without filter

          user             count fo transactions

              1                       2

              2                       1

In another Case I want the result to be filter based only on TransactionDate

[v-yulgu-msft]

Hi @Harshalb,

Please try below measures:

count transaction =
VAR temptb =
    SUMMARIZE (
        'transaction Tb',
        'transaction Tb'[transactiondate],
        "count", COUNT ( 'transaction Tb'[transactionid] )
    )
RETURN
    COUNTX ( temptb, [count] )

flag =
IF (
    ISFILTERED ( 'Dim Tb'[Year] ),
    IF ( MAX ( 'transaction Tb'[userid] ) = MAX ( 'User Tb'[User] ), 1, 0 ),
    1
)

Add measure [count transaction] to table visual together with 'Transaction Tb'[userid]. Add measure [flag] to visual level filter, set its value to a static number 1.

Best regards,

Yuliana Gu

[Harshalb]

I tried, It is not working

If you see the model the user and transaction relation is inactive.

I want the users created in particular period(selected in slicer) along with count of transaction they have made in that period.

I am adding the measure that i am using. Dont know why its not working

Transactions Made = CALCULATE(
DISTINCTCOUNT(Transactions[transaction_id])
,Users
,USERELATIONSHIP(Transactions[sender_swych_id],Users[swych_id]))

It is giving me the transaction count on the same date as the user created date

but i want the transaction count of that user for the month or year that is selected on the slicer i.e i want to filter with the dim date table