Forum Discussion

dushyant22j's avatar
dushyant22j
Frequent Visitor
6 years ago
Solved

How to find Inactive User from two tables with many 2 many relationship in PowerBI

I want find Inactive Users(Eg. Retailers)   so what I try to do is find users present in retailers table which are not present in Transaction table with date range and extra  filters as required ,...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  dushyant22j ,

     

    First create a calendar table as below:

    Calendar = CALENDAR(MIN('Retailer Table'[created_Date]),TODAY())

    Then create a measure as below:

    Measure2 = 
var _table_transaction=CALCULATETABLE(VALUES(Transactions[retailerid]),FILTER(ALL('Transactions'),'Transactions'[transaction_date]>=MINX(ALLSELECTED('Calendar'),'Calendar'[Date])&&'Transactions'[transaction_date]<=MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])))
Return
IF(MAX('Retailer Table'[retailerid]) IN _table_transaction,BLANK(),MAX('Retailer Table'[retailerid]))

    And you will see:

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
dushyant22j's avatar
dushyant22j
Frequent Visitor
6 years ago

Greg_Deckler  Thanks for reply.
I checked your pbix file and calculation for Lost Users.
But I am not understaning it how it will help to get list of Inactive users as per my requirement ?
 
Because in packt  , sturcture and relation beween tables are diffrent then my tables as shown in below image : 
Could you please Help  ?

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  dushyant22j ,

     

    First create a calendar table as below:

    Calendar = CALENDAR(MIN('Retailer Table'[created_Date]),TODAY())

    Then create a measure as below:

    Measure2 = 
var _table_transaction=CALCULATETABLE(VALUES(Transactions[retailerid]),FILTER(ALL('Transactions'),'Transactions'[transaction_date]>=MINX(ALLSELECTED('Calendar'),'Calendar'[Date])&&'Transactions'[transaction_date]<=MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])))
Return
IF(MAX('Retailer Table'[retailerid]) IN _table_transaction,BLANK(),MAX('Retailer Table'[retailerid]))

    And you will see:

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!