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
    6 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!
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

dushyant22j , Use this measure with a common retail dimension .

or with a column from the Retailer table


M1 = calculate(count(Transactions[retailerid]))
M2 = calculate(count(retailerid[retailerid]))

if(not(isblank(M2)) && isblank([M1]), [M2], , blank())

dushyant22j's avatar
dushyant22j
Frequent Visitor
6 years ago

amitchandak 
Hi ,
I tried as you mentioned 
created 3 measures and 1 calculated column just for reference.

M1 = CALCULATE(COUNT(Ledger_Transaction_Redemption[ledger.account_holder]))
M2 = CALCULATE(COUNT(Retailers[retailers.id]))
inactive = IF(NOT(ISBLANK([M2])) && ISBLANK([M1]),[M2],BLANK())
column - inactive Retailer = IF(NOT(ISBLANK([M2])) && ISBLANK([M1]),[M2],BLANK())

 

But when I tried to add filter based on transaction_date range , above dax (inactive)  give blank value.
So what should I do if I want to add date range filter ? Based on Transaction Date ? 

Example :  Transaction Date between 1st Jan,2020 and Today --  which users  are not in trasnaction table ?
                or  Transaction Date range  since last 3 months ,   which users entry not present in transaction table ? 
                            6 month --- etc ?? 

Could you please help ?