Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Measure for counting strings that belong to different groups

Need help to create a measure that can count the number of entities getting impacted under 2 different regions. My data table looks like below:

EntitiesRegion
A / BEMEA
CEMEA
C / DEMEA
C / DEMEA
A / B / C / DEMEA

There is further segregation of EMEA into UK and EU wherein:

UKGermany
AC
BD

My chart should give a count of 2 entities for UK and 4 entities for Germany. Please guide.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    If you want Year appears in the columns and Regions in rows, then you can't use calculated column, you can only use measure, and it becomes cumbersome to achieve your desired results.
    First you need to create a table like this:

    Build the relationships:

    Use these DAXs to create measures:

    EntitiesContain_UKCount = 
SUMX(
    Table1,
    IF(
        NOT ISBLANK(Table1[Entities]),
        IF(
            CALCULATE(
                COUNTROWS(Table2),
                FILTER(
                    ALL(Table2),
                    CONTAINSSTRING(Table1[Entities], Table2[UK])
                )
            ) > 0,
            1,
            0
        ),
        0
    )
)
    EntitiesContain_GermanyCount = 
SUMX(
    Table1,
    IF(
        NOT ISBLANK(Table1[Entities]),
        IF(
            CALCULATE(
                COUNTROWS(Table2),
                FILTER(
                    ALL(Table2),
                    CONTAINSSTRING(Table1[Entities], Table2[Germany])
                )
            ) > 0,
            1,
            0
        ),
        0
    )
)
    Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Country]) = "UK", 'Table1'[EntitiesContain_UKCount],
    SELECTEDVALUE('Table'[Country]) = "Germany", 'Table1'[EntitiesContain_GermanyCount]
)

    Then build the matrix like this:

     

    In addition, after testing, the DAX provided before has no problems in my test environment. It is indeed possible that some relationships in your data model cause your DAX to return incorrect results. Please try to delete the relationship or make the relationship Inactive.

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please try these DAXs:

    EntitiesContain_UKCount = 
SUMX(
    Table1,
    IF(
        NOT ISBLANK(Table1[Entities]),
        IF(
            CALCULATE(
                COUNTROWS(Table2),
                FILTER(
                    ALL(Table2),
                    CONTAINSSTRING(Table1[Entities], Table2[UK])
                )
            ) > 0,
            1,
            0
        ),
        0
    )
)
    EntitiesContain_GermanyCount = 
SUMX(
    Table1,
    IF(
        NOT ISBLANK(Table1[Entities]),
        IF(
            CALCULATE(
                COUNTROWS(Table2),
                FILTER(
                    ALL(Table2),
                    CONTAINSSTRING(Table1[Entities], Table2[Germany])
                )
            ) > 0,
            1,
            0
        ),
        0
    )
)

    And the final output is as below:


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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Dino,

      Thanks for the solution. It gives the correct count using card but I want to use Matrix to show year-wise split. For that I tried to add conditional column to my data set to include UK and Germany whereever the text contains entities as per my second table. However, it didn't work. Can you please help with this problem too?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        I'm sorry I'm not sure I'm understanding you correctly?
        You mentioned year-wise split, so I updated the test dataset a bit:

        And you mentioned add conditional column, so I use these DAXs to create two calculated columns:

        UK = 
IF(
    CALCULATE(
        COUNTROWS(Table2),
        FILTER(
            ALL(Table2),
            CONTAINSSTRING(Table1[Entities], Table2[UK])
        )
    ) > 0,
    1,
    0
)
        Germany = 
IF(
    CALCULATE(
        COUNTROWS(Table2),
        FILTER(
            ALL(Table2),
            CONTAINSSTRING(Table1[Entities], Table2[Germany])
        )
    ) > 0,
    1,
    0
)

        And since you want to use Matrix, so I build the Matrix like below:

        Is this what you want? I do not quite understand what you are looking for, if this is not what you are looking for, could you explain your desired outcome in more detail or show your desired outcome in the form of sample data?

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Dino,

    Thank you! This is exactly what I was looking for. However, it would have been nicer if the table could be transposed that is Year appears in the columns and Regions in rows.

    Another bizarre thing is .. while this code works absolutey fine for calculating UK but the count of German is returning 1 for all the entities and 0 only where there are no entities at all. Could it be something related to relationships?

    Please help.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

      If you want Year appears in the columns and Regions in rows, then you can't use calculated column, you can only use measure, and it becomes cumbersome to achieve your desired results.
      First you need to create a table like this:

      Build the relationships:

      Use these DAXs to create measures:

      EntitiesContain_UKCount = 
SUMX(
    Table1,
    IF(
        NOT ISBLANK(Table1[Entities]),
        IF(
            CALCULATE(
                COUNTROWS(Table2),
                FILTER(
                    ALL(Table2),
                    CONTAINSSTRING(Table1[Entities], Table2[UK])
                )
            ) > 0,
            1,
            0
        ),
        0
    )
)
      EntitiesContain_GermanyCount = 
SUMX(
    Table1,
    IF(
        NOT ISBLANK(Table1[Entities]),
        IF(
            CALCULATE(
                COUNTROWS(Table2),
                FILTER(
                    ALL(Table2),
                    CONTAINSSTRING(Table1[Entities], Table2[Germany])
                )
            ) > 0,
            1,
            0
        ),
        0
    )
)
      Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Country]) = "UK", 'Table1'[EntitiesContain_UKCount],
    SELECTEDVALUE('Table'[Country]) = "Germany", 'Table1'[EntitiesContain_GermanyCount]
)

      Then build the matrix like this:

       

      In addition, after testing, the DAX provided before has no problems in my test environment. It is indeed possible that some relationships in your data model cause your DAX to return incorrect results. Please try to delete the relationship or make the relationship Inactive.

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