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

[dushyant22j]

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
retailerid	name	country	city	created_Date	deviceid
1	A	India	jaipur	01-01-2020	aa
2	B	India	banglore	02-01-2020	bb
3	C	India	pune	03-01-2020	cc
3	C	India	pune	03-01-2020	gg
4	D	India	delhi	04-01-2020	dd
5	E	India	mumbai	05-01-2020	ee
5	E	India	mumbai	05-01-2020	iij

Transactions
retailerid	transactionid	transaction_date	transaciontype
1	11	01-02-2020	purchase
1	12	01-02-2020	purchase
2	13	03-02-2020	redemption
2	14	04-02-2020	signup
3	15	05-02-2020	purchase

Desired Output :

retailerid	name	country	city
4	D	India	delhi
5	E	India	mumbai

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

[amitchandak]

@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]

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

[dushyant22j]

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 ?

[Greg_Deckler]

@dushyant22j Chapter 6, Recipe 3 of DAX Cookbook has a Lost customer pattern. You can get the DAX here: https://github.com/gdeckler/DAXCookbook

If you are still having issues, @ me.

[dushyant22j]

@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 · Accepted solution]

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!