Measure for counting strings that belong to different groups

[tc1311]

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:

Entities	Region
A / B	EMEA
C	EMEA
C / D	EMEA
C / D	EMEA
A / B / C / D	EMEA

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

UK	Germany
A	C
B	D

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

[tc1311]

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.

[v-junyant-msft · Accepted solution]

Hi @tc1311 ,

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.

[v-junyant-msft]

Hi @tc1311 ,

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.

[tc1311]

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?

[v-junyant-msft]

Hi @tc1311 ,

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.