Create a column in the assignment table with families of other columns and tables

[Syndicate_Admin]

Hello everyone,

What I want is to create a column in the Orders table that is my fact, with some names of some columns in the same table and with some names of other columns in other tables.

EncargosAnx[propiedad_2] = DI , ME

EncargosAnx[propiedad_3] = LCP, LCC

Family[families] = SUN, COLD

This is the union of tables among which I mention, through a column called familias_id

What I want is to create a column in Orders, containing all the names I mentioned above, and only those names, to call the column Families, and be able to filter in my report by families, but all unified in one column

Thank you very much and I appreciate any help.

[Anonymous · Accepted solution]

Hi, @Syndicate_Admin 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.Based on your description, I have created the following test data:

2.I have provided two solutions for you:

Measure:

FamiliesMeasure = 
VAR prop2 = 
    IF (
        MAX('EncargosAnx(Orders)'[propiedad_2]) IN {"DI", "ME"},
        MAX('EncargosAnx(Orders)'[propiedad_2]),
        BLANK()
    )
VAR prop3 = 
    IF (
        MAX('EncargosAnx(Orders)'[propiedad_3]) IN {"LCP", "LCC"},
        MAX('EncargosAnx(Orders)'[propiedad_3]),
        BLANK()
    )
VAR family = 
    CALCULATE (
        VALUES('Familias(Family)'[families]),
        FILTER (
            'Familias(Family)',
            'Familias(Family)'[familias_id] = SELECTEDVALUE('EncargosAnx(Orders)'[familias_id]) &&
            'Familias(Family)'[families] IN {"SUN", "COLD"}
        )
    )
RETURN
    IF(ISINSCOPE('EncargosAnx(Orders)'[Year ]),CONCATENATE (
        CONCATENATE (prop2 & ",", prop3 & ","),
        family
    ),BLANK())

Calculated Column:

FamiliesColumn = 
VAR prop2 = 
    IF (
        'EncargosAnx(Orders)'[propiedad_2] IN {"DI", "ME"},
        'EncargosAnx(Orders)'[propiedad_2],
        BLANK()
    )
VAR prop3 = 
    IF (
        'EncargosAnx(Orders)'[propiedad_3] IN {"LCP", "LCC"},
        'EncargosAnx(Orders)'[propiedad_3],
        BLANK()
    )
VAR family = 
    CALCULATE (
        VALUES('Familias(Family)'[families]),
        FILTER (
            'Familias(Family)',
            'Familias(Family)'[familias_id] = 'EncargosAnx(Orders)'[familias_id] &&
            'Familias(Family)'[families] IN {"SUN", "COLD"}
        )
    )
RETURN
    CONCATENATE (
        CONCATENATE (prop2 & ",", prop3 & ","),
        family
    )

3.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.When uploading a file, please be careful to delete sensitive information.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.