Forum Discussion

ManchevB's avatar
ManchevB
Icon for Helper II rankHelper II
1 year ago
Solved

PowerBI breaking a table from formula

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

 

Need Help
  • ManchevB's avatar
    ManchevB
    1 year ago

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

6 Replies

  • ManchevB's avatar
    ManchevB
    Icon for Helper II rankHelper II
    1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi ManchevB ,

       

      Thanks for your feedback.

       

      Best regards,

      Adamk Kong

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      ManchevB
      Icon for Helper II rankHelper II
      1 year ago

      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 )

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.