topic Re: Build a DAX table from another tables in DAX Commands and Tips

Build a DAX table from another tables

DGPBi — Mon, 02 Sep 2024 08:27:40 GMT

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

Re: Build a DAX table from another tables

Gabry — Mon, 02 Sep 2024 08:46:36 GMT

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

Re: Build a DAX table from another tables

DGPBi — Mon, 02 Sep 2024 09:36:59 GMT

Hi Gabri,

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

Re: Build a DAX table from another tables

Gabry — Mon, 02 Sep 2024 10:03:01 GMT

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

Re: Build a DAX table from another tables

DGPBi — Mon, 02 Sep 2024 14:25:03 GMT

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 ?

Re: Build a DAX table from another tables

Gabry — Mon, 02 Sep 2024 14:40:33 GMT

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

Do you have any other calculated table?

Planon208 is a calculated table?

Re: Build a DAX table from another tables

DGPBi — Mon, 02 Sep 2024 16:06:37 GMT

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.

Re: Build a DAX table from another tables

Gabry — Mon, 02 Sep 2024 16:05:39 GMT

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

Re: Build a DAX table from another tables

DGPBi — Tue, 03 Sep 2024 06:26:22 GMT

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

Re: Build a DAX table from another tables

Gabry — Tue, 03 Sep 2024 07:04:29 GMT

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 )

Re: Build a DAX table from another tables

DGPBi — Tue, 03 Sep 2024 15:12:14 GMT

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