topic Re: DAX Calculation for Distinct Count in DAX Commands and Tips

DAX Calculation for Distinct Count

Revati25 — Fri, 30 Jan 2026 08:46:18 GMT

I am trying to replicate a Tableau formula in Power BI, however I am not able to achieve the result

Tableau Formula is:

{ FIXED [Name], [Task]: SUM(IF [Team] = 'Paris' or [Team] = 'Germany' or [Team] = 'Poland' then 1 END)}>0 THEN 'Centralized'

ELSEIF

{ FIXED [Client_Name], [Task Grouping]: SUM(IF [Team] <> 'Paris' and [Team] <> 'Germany' and [Team] <> 'Poland' then 0 END)}=0 then 'Non Centralized' END

Here the expected result is:

Paris	Germany	Poland	Any Other Team	Any Other Team	Centralized Count	Non Centralized Count
1	1	1	1	0	1	0
1	1		1	0	1	0
0	1	0	0	1	1	0
1	0	1	0	0	1	0
1	0	0	1	0	1	0
0	0	0	1	0	0	1
0	0	1	1	0	1	0
0	1	0	1	0	1	0
0	0	0	1	1	0	1

Re: DAX Calculation for Distinct Count

MohitsinghMS — Fri, 30 Jan 2026 09:09:28 GMT

Hi Revati25 ,

Create a Calculated Column to mimic the FIXED LOD behavior:

Status =
VAR CentralizedCount =
CALCULATE (
COUNTROWS ( 'YourTable' ),
ALLEXCEPT ( 'YourTable', 'YourTable'[Name], 'YourTable'[Task] ), -- Groups by Name & Task
'YourTable'[Team] IN { "Paris", "Germany", "Poland" }
)
RETURN
IF ( CentralizedCount > 0, "Centralized", "Non Centralized" )

To get the 1s and 0s from your image:

• Centralized Count: IF([Status] = "Centralized", 1, 0)

• Non Centralized Count: IF([Status] = "Non Centralized", 1, 0)

If this suggestion works for you, kindly mark this reply as the solution and provide kudos.

Mohit Singh | PL300 | DP600 | DP-700

https://www.linkedin.com/in/mohit-singh-16b917105

Re: DAX Calculation for Distinct Count

Revati25 — Fri, 30 Jan 2026 09:12:05 GMT

Hi MohitsinghMS

I have tried this calculation earlier, this is giving me double count.

That is the Name count is getting picked up if the name is present any other region as well as in those 3

Re: DAX Calculation for Distinct Count

MohitsinghMS — Fri, 30 Jan 2026 09:15:00 GMT

This happens because a Calculated Column adds a value to every row.
If a Name has 2 rows (e.g., 1 for "Paris" and 1 for "India"), the column marks both as "Centralized". When you drag that column to a visual and it sums them up, you get 2 instead of 1.
To fix this, you must use a Measure that looks at the distinct Names, not the individual rows.
The Solution: Use These Measures
Delete the calculated column (or stop using it in your visual) and use these Measures instead. They use SUMX and VALUES to ensure each Name is counted only once, regardless of how many rows it has.
1. Centralized Count (Measure)
This calculates unique Names that have at least one centralized team.
Centralized Count =
VAR CentralizedNames =
SUMX (
VALUES ( 'YourTable'[Name] ), -- Iterate through unique Names only
VAR HasCentralizedTeam =
CALCULATE (
COUNTROWS ( 'YourTable' ),
'YourTable'[Team] IN { "Paris", "Germany", "Poland" }
)
RETURN
IF ( HasCentralizedTeam > 0, 1, 0 )
)
RETURN
CentralizedNames

2. Non-Centralized Count (Measure)
This calculates unique Names that have zero centralized teams.
Non Centralized Count =
VAR NonCentralizedNames =
SUMX (
VALUES ( 'YourTable'[Name] ), -- Iterate through unique Names only
VAR HasCentralizedTeam =
CALCULATE (
COUNTROWS ( 'YourTable' ),
'YourTable'[Team] IN { "Paris", "Germany", "Poland" }
)
RETURN
IF ( HasCentralizedTeam = 0, 1, 0 ) -- Only count if NO centralized team found
)
RETURN
NonCentralizedNames

Why this works
* VALUES ( 'YourTable'[Name] 😞 Creates a temporary list of unique names.
* SUMX: Goes through that unique list one by one.
* The Logic: It checks "Does this specific Name have a row in Paris, Germany, or Poland?"
* If Yes: It scores it as a 1 for Centralized.
* If No: It scores it as a 1 for Non-Centralized.
* Result: Even if a Name has 50 rows, it is only evaluated and counted once.

Re: DAX Calculation for Distinct Count

Revati25 — Fri, 30 Jan 2026 10:11:52 GMT

If I apply ALL Except with this for Task Group, that is coming from another table, would it work

Re: DAX Calculation for Distinct Count

MohitsinghMS — Fri, 30 Jan 2026 12:25:59 GMT

Please try

Re: DAX Calculation for Distinct Count

techies — Sat, 31 Jan 2026 09:28:11 GMT

Hi Revati25 Can you please share sample datasets to test?

Re: DAX Calculation for Distinct Count

v-nmadadi-msft — Mon, 02 Feb 2026 10:11:24 GMT

Hi Revati25 ,
Thanks for reaching out to the Microsoft Fabric Community forum.

I have used this DAX measure to get the expected result

Centralization Type = VAR HasCentralTeam = CALCULATE ( COUNTROWS ( FactTasks ), ALLEXCEPT ( FactTasks, FactTasks[Name], FactTasks[Task] ), FactTasks[Team] IN { "Paris", "Germany", "Poland" } ) VAR HasOtherTeam = CALCULATE ( COUNTROWS ( FactTasks ), ALLEXCEPT ( FactTasks, FactTasks[Client_Name], FactTasks[Task Grouping] ), NOT FactTasks[Team] IN { "Paris", "Germany", "Poland" } ) RETURN SWITCH ( TRUE (), HasCentralTeam > 0, "Centralized", HasOtherTeam = 0, "Non Centralized", BLANK () )

Attaching the .pbix for reference

I hope this information helps. Please do let us know if you have any further queries.
Thank you