I need to compare two sets over two tables.
I have a set of assets each one with a set of prerequisites and a list of accounts, each one with a set of features. Prerequisites and features are of the same kind and have the same cardinality, I changed the name just for clarity.
I need to list for each asset how many accounts can apply for that asset. The condition is that the account has all the features required by the asset. So essentially if for the asset(x,y,z) all accounts with features (x,y,z) should be counted or listed. Also, I need to show for each account what asset can be applied, using the same logic.
I have to work in Excel so functions like TREATAS are out of question even if I can translate from PBI (thanks to @AntrikshSharma).
I put this topic in Data Model since I suppose modeling properly will ease the writing of DAX code, all the same I’m approaching with tables like in the attachment just using DAX, looping on virtual tables.
In your example you have assets A till D and accounts A till F.
Do you have a predefined set of requirements per asset, always the same per asset, or can it vary per asset ? Same question for account . (Can Account F have feature 1 and 3 together, and another combination of 4 and 5?)
Generally how many features and requirements do you have, quasi endless or fixed to just a couple ?
This has influence how to set up the data model.
Kind regards, deltaselect
requirements per asset may vary. Each asset can have from 1 to the max number of requirements. Same for the accounts
Features and requirements are under 20. BTW the two sets have the same domain, I just used different names for clarity: an asset has 1 or more requirements for being used, an account has 0 or more features for the adoption of one or more assets.
Account A has Feature 1, Feature 2, Feature 3, and Feature 4, so it can adopt Asset A(Reqs 1, Req2) Asset B(Reqs 2, Reqs 3), and Asset C(Reqs 4), but not Assed D(Reqs 4, Reqs 5) since in this last case Reqs 5 is not in the Features of Account A.
A way to the solution, how I would approach it, is :
make an unique key for the accounts with the features and the assets with the requirements to be able to connect those with the same features/requirements on both sides.
(for example, concatenate x and e for empty fields for all available features/requirements per account /asset) This would still work for 20 features/ req as well.
create the searched information via Power Query Merge Tables. (2 lists, accounts having the same requirements as the assets and vice versa)
How to do in detail:
Upload the assets and accounts with the requirements/features
To be done for both sides.
Replace null values for nothing
Replace nothing for “e”
Add a custom column , concatenate all columns with “&”, creating the key.
If you have lots of text, (instead of “x” in your example), categories or first 3 letters will do (?)
Merge Queries as New, select Join Kind Outer Left
Expand the new column, columns can be removed, the list can be filtered to get rid of null values.
It results in the 2 required lists: an expanded list of Accounts combined with the related Assets and vice versa; you can then obtain the required Excel reporting in Power BI.
Data in (table) visuals and from Power Query can however be exported into Excel. Listing Assets w accounts and vice versa.pbix (81.0 KB) BaseData3.xlsx (12.7 KB)
Hi @Roberto, did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @deltaselect,
thanks very much for your really appreciated answer.
I have in mind something I still couldn’t work on. When done I’ll post for sharing.
Thanks again
Hi Roberto,
I hope this solution suits you. This solution is an exact match, it list the accounts with
the same requirements as the assets.
Second thoughts:
Rereading your question, and remarks, do you want to have the “over-qualified” accounts included in the report(s) ?
If it would be possible to develop an unique and meaningful, qualifying number for all possible requirement combinations (!) , it could determine if the accounts qualifies for the asset. (account nr >= asset nr)
Hi @deltaselect
sorry for the late response.
I just wanted to share my progress. The road is still long, but I start to see something.
My approach is using more DAX than data modeling. So formulas are more complex, but it’s a kind of personal challenge.
I was almost able to list the assets the fit a specific account, but the logic is the same for the opposite direction. It’s still buggy when the account has only 1 requirement. Debugging is a nightmare
I need to deliver the full model by tomorrow then I’ll post the solution right after.
Hi @Sam,
after some trying I was able to get what I needed.
The attached model is very simple. Actually I didn’t want to push on data modeling this time to try understand DAX in this setup.
The problem can be resumed in a single statement: I’ve 2 lists, one of customers, for each one there is a set of features, and a list of assets, each one with a list of requirements. I need to list the assets that fit each customer, and also show the list of customers that asset can applied to.
In the attached model I wrote a measure that actually does it (it fails when the customer has just one feature, but with some more debugging I bet I can fix on my own).
Since I started to concatenate assets now I’m a kind of stuck since what I need it a table of assets I can use for other filtering. How can I transform this below to get instead of a concatenate string a table?
Thanks for support and awesome work.
Roberto
Assets the fits to the selected account =
// shows assets applicable for the selected account
VAR ListOfFeaturesPerAccount =
// for the account in context builds the list of requirements
SUMMARIZE(
FILTER( Accounts, Accounts[Value] = “x” ),
Accounts[Attribute]
)
RETURN
CONCATENATEX(
Assets,
VAR Asset = Assets[Asset]
VAR ListOfFeaturesPerAsset =
SUMMARIZE(
FILTER( Assets, Assets[Asset] = Asset),
Assets[Attribute]
)
// here I'm testing if the list of features of the asset is equal or a subset of features of the account
VAR Intersection =
INTERSECT( ListOfFeaturesPerAccount, ListOfFeaturesPerAsset )
RETURN
IF( ISEMPTY( EXCEPT( ListOfFeaturesPerAsset, Intersection ) ), [Asset] , BLANK() ),
";"
)
