Help! Cannot figure out how to lookup values and update status due to circular dependency error

[dehmos27]

I have an interesting situation and am unsure how to accomplish it. I need to use Power BI to project the number of licenses needed for a particular product. Here are the datasets (sample .PBIX here).

Users

Name	License Needed	License Need Expire	Status
Bill	1/1/2020	1/31/2020	Inactive
Jane	1/15/2020	2/14/2020	Active
Mark	2/1/2020	2/28/2020	Active

The Status column is based on whether or not a "Projected Date" (e.g., 2/1/2020) has passed the "License Need Expire" date. If it has, the user needs to be deactivated; if the date has not yet arrived, the user needs an active license.

Here is a sample of the other table:

Licenses

Index	License
1	A
2	B
3	C
4	D

For all "active" users, I want to do some kind of lookup to find the first UNUSED license from the Licenses table and have the license be marked as "In Use" when assigned. This will cause the user in the next row to receive a different license and so on; the license itself is irrelevant as I simply need users to have an ACTIVE license.

Here is an example of what I'd like the Users table to look like if the ProjectedDate is 2/1/2020:

Users

Name	License Needed	License Need Expire	Status	License
Bill	1/1/2020	1/31/2020	Inactive	-
Jane	1/15/2020	2/14/2020	Active	A
Mark	2/1/2020	2/28/2020	Active	B

And here is what I'd like the Licenses table to look like:

Index	License	Status
1	A	In Use
2	B	In Use
3	C	Available
4	D	Available

Hope I'm making sense and help from the community would be profoundly appreciated. I have the code written up but am getting a circular dependency error in the file to which I linked above.

[AllisonKennedy]

@dehmos27  Do you need the status in the license table? That is definitely a circular dependency, maybe it could be avoided using a variable or calculating the license status column virtually within the MEASURE for the license column in the user table? Yes, I would use a Measure so that it can be dynamic. Since you say the actual license number doesn't matter, the measure will be dynamic and if one user expires, it will just shift the licenses. Do you need the actual letter codes or would a count be sufficient?

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[AllisonKennedy]

@dehmos27  Try these calculated columns instead: 

License Status = IF(COUNTROWS(FILTER(Users,Users[User Status]="Active"))<Licenses[Index],"Available","In Use")

User Status = IF(Users[License Need Expire]<[ProjectedDate],"Inactive", "Active")

It won't assign the licence to the user (that becomes circular) but can at least project which ones are available for you.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv