Forum Discussion

DGPBi's avatar
DGPBi
Icon for Helper I rankHelper I
2 years ago
Solved

Build a DAX table from another tables

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's avatar
    Gabry
    2 years ago

    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
)

10 Replies

  • Gabry's avatar
    Gabry
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      DGPBi
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Gabry
        Icon for Super User rankSuper User
        2 years ago

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