SUMX with Distinct count on different column

[Leigh_123]

Hi all,

I have calculation that i have created from online research that counts string column ('Query1'[Business(es) Area]) and filters by a predefined table 'Business area filter':

I want to be able to have a distinct count of the SUMX but based on a different column in 'Query1', is this something that is possible?

current dax below

Business area =
VAR __SearchTerms =
    ADDCOLUMNS (
        'Business area filter',
        "Count",
            COUNTROWS (
                FILTER (
                    'Query1',
                    FIND ( 'Business area filter'[Business Area reference],'Query1'[Business(es) Area],, 0 ) > 0
                )
            )
    )
RETURN
    SUMX ( __SearchTerms, [Count] )

[Anonymous · Accepted solution]

Hi @Leigh_123 ,

Please have a try.

Business area =
VAR __SearchTerms =
    ADDCOLUMNS (
        'Business area filter',
        "Count",
            COUNTROWS (
                FILTER (
                    'Query1',
                    FIND ( 'Business area filter'[Business Area reference],'Query1'[Your column name],, 0 ) > 0
                )
            )
    )
RETURN
    SUMX ( DISTINCT(__SearchTerms, 'Query1'[Your column name]), [Count] )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Leigh_123]

ID	Business Area	Country
1	Tax; Consulting	Korea
2	Tax	France
3	Audit	UK
4	Audit	Ireland
5	Audit; Tax; Consulting	Australia
6	Tax	China
7	Audit; Tax	Germany
8	Consulting	Zimbabwe
9	Consulting; Tax	South Africa
10	Consulting; Tax	US
11	Audit	Switzerland
12	Audit	Switzerland
13	Financial Advisory	India
14	Risk Advisory	Japan
15	Audit; Tax	Germany
16	Risk Advisory	Spain
17	Financial Advisory; Audit	Norway
18	Audit; Tax	Spain
19	Enabling Area	Egypt
20	Enabling Area	Egypt
21	Audit; Tax	Belgium
22	Tax	Greece
23	Tax	Italy
24	Conulting; Tax; Audit	Turkey
25	Audit; Enabling Area	Portugal
26	Audit	Latvia
27	Audit; Tax	Lithuania
28	Audit; Tax; Consulting; Risk Advisory	Georgia
29	Financial Advisory; Audit; Tax	Barbados

Providing the attached sample data. Escentially i need a distint count of "Business Area" based on "Country". I can do this with individual dax for each bsiness area:

count (A&A) = 
Calculate(
    DISTINCTCOUNT('Dummy data'[Country]),
    CONTAINSSTRING('Dummy data'[Business Area],"Audit"))

However if i want to add this to a bar chart i get a mess:

I am looking to achieve the above in the format below:

I achieved this with the folowing Dax:

Business area percentage = 
VAR __SearchTerms =
    ADDCOLUMNS (
        'Business area filter',
        "Count",
            COUNTROWS (
                FILTER (
                    'Dummy data',
                    FIND ( 'Business Area Filter'[Business Area reference],'Dummy data'[Business Area],, 0 ) > 0
                )
            )
    )
RETURN
    SUMX ( __SearchTerms, [Count] )

Is there any way to edit this to count distinct? Apologies, i am unable to provide pbix.

Thank you in advance

[lbendlin]

I would recommend you solve that issue in Power Query to bring your source data into a usable format.  Then the visuals will work automatically.

Note you have a data quality issue with "Conulting".

See attached