Forum Discussion

GrahamR99's avatar
GrahamR99
Resolver I
4 years ago
Solved

Create a relationship using a between two columns

Hello 

I'm query Microsoft Business Central.  It has two tables.  The data table with EntityID, and a user data table which has "EntityID from" and "EntityID to".

 

If my EntityID is 6, how do I create a relationship to the user data table if "EntityID from" is 1 and "EntityID to" is 10. as 6 is between 1 and 10?

 

Is this possible?

 

The user data table has a user column that I want to show on the data table.

 

Regards

 

Graham

 

  • In the query editor, you can define a custom column as a list:

    {[#"From Entry No."]..[#"To Entry No."]}

     Remove unneeded columns and expand the list column.

     

    Here's a full M query you can past into your Advanced Editor:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1Dc00DcyMAJx3IsSMxJzFYLyk7OBPM8QIOGUWJKcYWAAkgVjU6VYnWglI5BOIySdwZm5+XkKTolgjcGJOanFCL0gxYZmQMLIDKzZGKTZGEmzV35GnoJLfnkqpl6QWiNzIGFsoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"No." = _t, #"Creation Date" = _t, #"User ID" = _t, Source = _t, #"Journal Batch Name" = _t, #"From Entry No." = _t, #"To Entry No." = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"Creation Date", type date}, {"User ID", type text}, {"Source", type text}, {"Journal Batch Name", type text}, {"From Entry No.", Int64.Type}, {"To Entry No.", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Entrie No.", each {[#"From Entry No."]..[#"To Entry No."]}, type list),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"No.", "From Entry No.", "To Entry No."}),
        #"Expanded Entrie No." = Table.ExpandListColumn(#"Removed Columns", "Entrie No."),
        #"Reordered Columns" = Table.ReorderColumns(#"Expanded Entrie No.",{"Entrie No.", "Creation Date", "User ID", "Source", "Journal Batch Name"})
    in
        #"Reordered Columns"

8 Replies

  • Not possible. Relationships have to match keys, not just compare them.

     

    The solution would be to expand the user data table to have one row per entity.

  • YukiK's avatar
    YukiK
    Impactful Individual

    I don't think this is possible in DAX itself. I'd do this in your ETL process where maybe adding a new key column that would connect to EntityID.