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 ,

 

Source Tables in Power bi  :
Relationship between them are  :  Many to Many 

Retailer Table     
retaileridnamecountrycitycreated_Datedeviceid
1AIndiajaipur01-01-2020aa
2BIndiabanglore02-01-2020bb
3CIndiapune03-01-2020cc
3CIndiapune03-01-2020gg
4DIndiadelhi04-01-2020dd
5EIndiamumbai05-01-2020ee
5EIndiamumbai05-01-2020iij

 

Transactions   
retaileridtransactionidtransaction_datetransaciontype
11101-02-2020purchase
11201-02-2020purchase
21303-02-2020redemption
21404-02-2020signup
31505-02-2020purchase

 

 

Desired Output :

retaileridnamecountrycity
4DIndiadelhi
5EIndiamumbai

 

So how to find this ?    New Dax table ?  calculated columns ?  Calculated Measure >??

 

FYI : Just note if I want to do same thing in Database then its Equivalent SQL query in databse
( In database i have three tables Retailer , transaction , ledger  --- but when did modeling in power bi I merged (leder + Transaction) as one table and  keep Retailer table as it is.
 
select * from retailers
where retailers.id not in
(select ledger.retailerid from ledger
join transactions
on ledger.transactiond = transactions.id
where transactions.date between ("01-01-2020" and TODAY);

Please guide me through above ?

Extra question :  1)Does Many to Many relationship is ok to have in Power BI ?  Or Any disadvantages you guys can think of ? 

2) How to learn Dax from basics ? -- Any Document or Udemy Course recommanded for Dax ?? 

@need help , dax Help , @need DAX Help

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!

6 Replies

  • 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

      Thanks for reply.

      But I am not getting it how to use them ?
      Are you saying that  I have to create 3 Measures ?  or only 1 Measure ?
      I tried to create 1 Measure but it show error as there two variable like M1 =  & M2 =

      See below Image : 

       



      Could you please paste code here ?

    • 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 ?