PowerBI breaking a table from formula

[ManchevB]

Hi all, 

I have this PBI expression that I am working on here: 

SkillsCountriesTable =

ADDCOLUMNS(

    VALUES('Skills Data'[SkillName]),  -- Get all distinct skills

    "CountryWithSkill",

    CONCATENATEX(

        CALCULATETABLE(

            VALUES('Employees'[CountryName]),  -- Get countries for each skill

            'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])

        ),

        'Employees'[CountryName], ", "  -- Concatenate countries that have the skill

    ),

    "CountriesWithSkill",

    CONCATENATEX(

        CALCULATETABLE(

            VALUES('Employees'[CountryName]),  -- Get countries for each skill

            'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])

        ),

        'Employees'[CountryName], ", "

    ),

    "CountriesWithoutSkill",

    CONCATENATEX(

        FILTER(

            VALUES('Employees'[CountryName]),  -- Get all countries that do not have the skill

            NOT('Employees'[CountryName] IN

                CALCULATETABLE(

                    VALUES('Employees'[CountryName]),

                    'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])

                )

            )

        ),

        'Employees'[CountryName], ", "

    )

)

The result is:

and what I would like to have is:

in CountryWithSkill - to have each country separately and not in 1 line

in CountriesWithoutSkill - to have each country separately and not in 1 line ( these are the countries that doesn't have that skill )

in CountriesWithSkill - to have each country separately and not in 1 line ( these are the countries that also have the respective skill )

I am available if more information is needed.

Appreciate everyone who would be able to advise. 

Thank you,

Boris

[ManchevB · Accepted solution]

I managed to split the table CountriesWithoutSkill into a new column and then applied page filters to see only what is relevant

[v-kongfanf-msft]

Hi @ManchevB ,

Thanks for your feedback.

Best regards,

Adamk Kong

[v-kongfanf-msft]

Hi @ManchevB ,

Maybe you can modify formula like below:

SkillsCountriesTable = 
ADDCOLUMNS(
    VALUES('Skills Data'[SkillName]),
    "CountryWithSkill",
    CALCULATETABLE(
        VALUES('Employees'[CountryName]),
        'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])
    ),
    "CountriesWithSkill",
    CALCULATETABLE(
        VALUES('Employees'[CountryName]),
        'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])
    ),
    "CountriesWithoutSkill",
    EXCEPT(
        VALUES('Employees'[CountryName]),
        CALCULATETABLE(
            VALUES('Employees'[CountryName]), 
            'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])
        )
    )
)

Best Regards,
Adamk Kong

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

[Ashish_Mathur]

Hi,

If you do not want them in a single linem then why are you using the CONCATENATEX() function?  Share some data to work with, explain the question and show the expected result.

[FarhanJeelani]

Hi @ManchevB ,

It looks like the issue is that your CONCATENATEX function is combining all countries into a single line, but you want each country listed separately, presumably one per row.

To achieve this, you don’t need to use CONCATENATEX  at all, as it is meant for combining values into a single text string. Instead, you can create separate tables or adjust your data model.

Here’s a solution:

Modify Your DAX to Create Separate Rows

SkillsCountriesTable =
SELECTCOLUMNS(
    CROSSJOIN(
        VALUES('Skills Data'[SkillName]),
        VALUES('Employees'[CountryName])
    ),
    "SkillName", 'Skills Data'[SkillName],
    "Country", 'Employees'[CountryName],
    "HasSkill",
    IF(
        'Employees'[CountryName] IN
            CALCULATETABLE(
                VALUES('Employees'[CountryName]),
                'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])
            ),
        "Yes",
        "No"
    )
)

 Please mark this post as solution if it helps you. Appreciate Kudos.

[ManchevB]

Thanks for the suggestion, I am aiming to have 3 columns:

in CountryWithSkill - to have each country separately and not in 1 line

in CountriesWithoutSkill - to have each country separately and not in 1 line ( these are the countries that doesn't have that skill )

in CountriesWithSkill - to have each country separately and not in 1 line ( these are the countries that also have the respective skill )