Build a DAX table from another tables

[DGPBi]

Hi everyone,

I have to build a new table with collect certain information (Company name, Legal Code, PlanonID) from my AAD users table. 

I built this : 
Now the request changed,  I need the same build + comparing if the PlanonID exists in 'Planon208' Table column [Code].
I have no idea on the way to build this ...

Locations = 
     FILTER(
        DISTINCT(
            SELECTCOLUMNS(ADUser,
                 "PlanonID",ADUser[extensionAttribute6],
                 "CompanyName",ADUser[organizationalPerson.company],
                 "LegalCode",ADUser[extensionAttribute11]
                 )),
        LEN([PlanonID])>0 && LEN([CompanyName])>0 && LEFT([PlanonID],4)="OBJ0" && LEN([LegalCode])>0
     )

Thanks in advance for your help

[Gabry]

Hello @DGPBi ,

isn't enough do add IN VALUES?

Locations =
FILTER(
DISTINCT(
SELECTCOLUMNS(ADUser,
"PlanonID",ADUser[extensionAttribute6],
"CompanyName",ADUser[organizationalPerson.company],
"LegalCode",ADUser[extensionAttribute11]
)),
LEN([PlanonID])>0 && LEN([CompanyName])>0 && LEFT([PlanonID],4)="OBJ0" && LEN([LegalCode])>0 && [PlanonID] IN VALUES('Planon208'[Code])
)

[DGPBi]

Hi Gabri,

Locations = 
     FILTER(
        DISTINCT(
            SELECTCOLUMNS(ADUser,
                 "PlanonID",ADUser[extensionAttribute6],
                 "CompanyName",ADUser[organizationalPerson.company],
                 "LegalCode",ADUser[extensionAttribute11]
            )),
        LEN([PlanonID])>0 && LEN([CompanyName])>0 && LEFT([PlanonID],4)="OBJ0" && LEN([LegalCode])>0 && [PlanonID] IN VALUES('Planon208'[Code])
     )

With this, I obtain an error message : "A circular dependency was detected: Locations[Locations], 8b77ea91-5664-b487-a140-3621e9975612, Locations[PlanonID], Locations[Locations].

[Gabry]

Sorry, can't look at that model 😢

You can try to do it as steps:

ADUserFiltered =
FILTER(
ADUser,
LEN(ADUser[extensionAttribute6]) > 0 &&
LEN(ADUser[organizationalPerson.company]) > 0 &&
LEN(ADUser[extensionAttribute11]) > 0 &&
LEFT(ADUser[extensionAttribute6], 4) = "OBJ0"
)

LocationsTemp =
DISTINCT(
SELECTCOLUMNS(
ADUserFiltered,
"PlanonID", ADUserFiltered[extensionAttribute6],
"CompanyName", ADUserFiltered[organizationalPerson.company],
"LegalCode", ADUserFiltered[extensionAttribute11]
)
)

Locations =
FILTER(
LocationsTemp,
LocationsTemp[PlanonID] IN VALUES('Planon208'[Code])
)

[DGPBi]

Thanks Gabry

Ok, so I have to build 3 tables vs 1 table?
After tried this solution so 2 new tables and updated the current table which have lot of relationships in place I still obtain the same error message: 
A circular dependency was detected: Locations[Locations], 8b77ea91-5664-b487-a140-3621e9975612, Locations[PlanonID], Locations[Locations].

Is there a way to have only 1 table ?

[Gabry]

Yeah I meant using variables, not building 3 separate tables.

Do you have any other calculated table?

Planon208 is a calculated table?

[DGPBi]

Hi, 
If I understood correctly I changed to:

Locations = 

VAR ADUserFiltered1 =
FILTER(
ADUser,
LEN(ADUser[extensionAttribute6]) > 0 &&
LEN(ADUser[organizationalPerson.company]) > 0 &&
LEN(ADUser[extensionAttribute11]) > 0 &&
LEFT(ADUser[extensionAttribute6], 4) = "OBJ0"
)

VAR LocationsTemp1 =
DISTINCT(
SELECTCOLUMNS(
ADUserFiltered1,
"PlanonID", ADUserFiltered[extensionAttribute6],
"CompanyName", ADUserFiltered[organizationalPerson.company],
"LegalCode", ADUserFiltered[extensionAttribute11]
)
)

VAR Locations1 =
FILTER(
LocationsTemp1,
LocationsTemp[PlanonID] IN VALUES('Planon208'[Code])
)

RETURN 
Locations1

but I receive this error:
A single value for column 'extensionAttribute11' in table 'ADUserFiltered' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

About the tables:

- Locations, ITOnSIte_ITSupport, ITOnsite_Contacts, ITOnsite_FullInfo are DAX calculated tables.
- LocationList, Planon208, Site Support- MFG, User Information List, ADUser are imported from different sources
- PlanonUnpivot, Role, are unpivot tables.

[Gabry]

Is it possible for you to load the pbix with masked data? I can't work this out like this sorry

[DGPBi]

PLease find here the link:
Gabry, I sent the link in PM.

[Gabry · Accepted solution]

Hello, 

based on your requests this is the formula for your table

Locations = 
var _filter = DISTINCT(Planon208[Code])
RETURN
     FILTER(
        DISTINCT(
            SELECTCOLUMNS(ADUser,
                 "PlanonID",ADUser[extensionAttribute6],
                 "CompanyName",ADUser[organizationalPerson.company],
                 "LegalCode",ADUser[extensionAttribute11],
                 "IsInternal",CONTAINSSTRING(ADUser[distinguishedName],"Internal")
            )),
        LEN([PlanonID])>0 && LEN([CompanyName])>0 && LEFT([PlanonID],4)="OBJ0" && LEN([LegalCode])>0 && [IsInternal]=TRUE() && [PlanonID] in _filter
)

[DGPBi]

Thanks a lot! ... The code is accepted and number of the locations seems OK.