cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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

forum.png

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

2 REPLIES 2
Highlighted
Microsoft
Microsoft

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

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.

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

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

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors