Hi @sam.mckay,
I’m still struggling with a model I already posted for some help Sets comparison over disconnected tables, but I could not make it yet.
.
I tried different solutions, both working on modeling and trying to write DAX code.
Essentially I’m trying to compare two sets (Account e Assets) and find those assets fitting for a specific account and accounts an asset can fit in.
For instance Account A can implement Asset A, Asset B, Asset C since Account requirements (1, 2, 3, 4) contains requirements the fit Asset A(1, 2), Asset B(2), Asset C (1, 2)
The same goes for Assets. Asset A(1, 2) fits in Accont A(1, 2, 3, 3, 5), Account C(1, 2), Account F(1, 2, 3, 4, 5)
I feel the solution is leveraging the DAX sets functions so I’m using disconnected tables.
Table cardinality is very small (under 100 rows), so efficiency is not an issue.
Sets can have different cardinalities, but for this example, I’m using the same names and the same number of elements.
I was able to make it work (almost) using concatenatex (for debugging purposes actually) but I need the list so I’m trying to filter with no luck.
I had never faced such a complex scenario, so my struggle.
Can you please help?
Thanks in advance
Roberto
Allocation - with relations.pbix (1.8 MB)
Allocation - without relations.pbix (1.8 MB)
allocation.xlsx (17.3 KB)
Hello @Roberto
Just trying to understand, Account A has requirements 1,2,3,4
and these requirements were also related to Asset A, B, C, D
And you are looking to find those Accounts that completely satisfies a particular Asset requirements or vice versa.
Is this correct?
Regards
Kumail Raza
Hi Kumal,
it’s correct.
As in the example, Account A has 4 requirements (or features) 1, 2, 3, 4.
For this account I can use Asset A which requirements are 1 and 2 (that is they are included in Account A requirements), Asset B which requirements are 2, 3, 4, also included in the Account A requirements set, and Asset C with requirement 4, included this one as well in Account A requirement set.
As you can see in the model Allocation - without relations.pbix the measure Assets the fits to the selected account CONCATENATEX make the work. Duplicated assets would be removed in the FILTER version, but I’m stuck there for weeks actually 
I suppose when this is solved traversing Asset → Account will be the very same.
Thanks a lot for your support.
Roberto
Assets the fits to the selected account CONCATENATEX =
// 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() ),
";"
)
Some small progress, but it’s not elegant at all.
> # Assets fitting selected account FILTER version =
> // 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] // Reqs1, Reqs2, Req3, ...
> )
> VAR ListOfRequirementsPerAsset =
> SUMMARIZE(
> FILTER( Assets, Assets[Value]= "x" ),
> Assets[Attribute]
> )
> return
> // when > 0 it's included set or a subset
> sumx(
> 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 ) ), 1 , 0 )
> )
Allocation - without relations.pbix (1.8 MB)
Hi Roberto,
Concept :
Base = Each Account to be compared with Asset and vice versa
Contra = Each Asset compared with each account and vice versa
Conditions to come to those listings are:
A) Account/Asset is Contra from Base (comparing assets with accounts and vice versa)
B) The Requirement Contra is the same as Base
C) Number of requirements per Contra is smaller or equal number requirements Base
D) Contra has NOT a requirement which Base does not have
Determining numbers (how many assets qualify per account and vice versa) is possible and available in Excel VBA, possibly with DAX as well (?), a list is something completely else . .
-
- How do you currently group or categorize which assets qualify for accounts and vice versa ?
-
- How should the report look like ?
From your example, with only 6 accounts, you have already some possibilities (you mentioned you have more then 20 requirements):
category1 : minimal req 1 or/and 2 or/and 3 or/and 4, not requirement 5
category2: only req 2, no other requirement
category3: min req 1 or/and req2, no other requirement
category4: min req 3 or/and req4, no other requirement
category5: min req 4 or/and req5, no other requirement
category5: all qualify
Kind regards, deltaselect
Hi @deltaselect,
thanks for chipping in again 
I really appreciate it
After many efforts, I was able to get what I needed. I had to lever on filters on UI (I don’t really like it), but I was unable to get results in tables as I wanted. I’ve some ideas in mind, but work is hectic and I don’t have much time for now.
All the work is based on comparing sets using functions intersect and except worked fine.
Answering your questions:
- assets’ features have to be a subset of accounts’. that’s all.
- no specific report look requests, so I whipped up an initial layout. Consumers will ask for more.
Check the attachment. Logic is pretty simple (now… 
)
I’m eager to receive feedback and suggestions
Thanks
Roberto
Allocation - without relations.pbix (60.2 KB)
Hi Roberto,
I am not sure how far you were with the reporting, but given your PBIX, progress is made:
-
Added a lookup table “Requirements”, and made a one to many relationship to the Account and Asset table.
-
Through this relationship it is possible to set up two matrix tables, using the (your!) DAX-formula “Assets fitting account (vice versa)”, with the requested lists Assets per Account and vice versa . The DAX formula works for both sides, listing Assets per Account and Accounts per Asset.
-
The Intersect and Except functions within the DAX formula works, however missing is the filtering out of Assets with Requirements, which are not available in the Accounts (and vice versa).
For example in Account A Asset D should be excluded as it have Requirement 5, which Account A does not have.
-
In your formula is selected upon the availability of “x”, being a requirement. Question is if this works with real live text data, with presumably different text for each requirement; possibly the lookup-table Requirements could help.
If it is known how to add the the missing filter in the DAX formula, (showstopper) I would think the report is, apart from the layout, possible.
See also attached PBIX
Allocation - with relations Roberto v2.pbix (61.1 KB)
Kind regards, deltaselect
1 Like
Thanks @deltaselect I’ll have real data very soon and the model will need some change, but the basis are good now
Hi @sam.mckay and all who helped me out.
Eventually I did it.
Allocation - without relations.pbix (60.2 KB)
allocation.xlsx (19.1 KB)
Thanks all
Roberto
