topic Re: DAX Count() with some records counted twice in DAX Commands and Tips

DAX Count() with some records counted twice

Marpas — Thu, 06 Feb 2025 10:46:51 GMT

Hi all,
I would need DAX that would count records for me, which would:
1. some categories merge into one total

2. one category would be counted twice

input:

Category	Count
A.1	10
A.2	20
A.1 + B	30
B	40
C	50
D	60
E	70
F	80

output:

Category	Total
A	60
B	70
C	50
D	60
E	70
F	80

if it was only point 1 (merging) I would simply make custom field where A1 & A2 would be changed to A and just run count() on that field, but unfortunately that "A.1 + B" category which needs to be counted towards total of A and total of B makes things harder.

Thanks in advance for any suggestions.

Re: DAX Count() with some records counted twice

bhanu_gautam — Thu, 06 Feb 2025 11:45:36 GMT

Marpas First Create a calculated column to merge categories:

dax
MergedCategory =
SWITCH(
TRUE(),
Table[Category] = "A.1", "A",
Table[Category] = "A.2", "A",
Table[Category] = "A.1 + B", "A",
Table[Category] = "B", "B",
Table[Category] = "A.1 + B", "B",
Table[Category] = "C", "C",
Table[Category] = "D", "D",
Table[Category] = "E", "E",
Table[Category] = "F", "F",
Table[Category] -- Default case
)

Create a calculated table to count the records:

dax
CategoryTotals =
SUMMARIZE(
Table,
Table[MergedCategory],
"Total",
SUMX(
FILTER(
Table,
Table[MergedCategory] = EARLIER(Table[MergedCategory])
),
Table[Count]
)
)

Re: DAX Count() with some records counted twice

johnt75 — Thu, 06 Feb 2025 11:47:50 GMT

I would create a helper table with 2 columns, one for the category name you want to use in visuals ( e.g. A ) and one with the category name from the fact table ( e.g. A.1 ). There would be multiple rows for categories A and B.

Create a many-to-many single direction relationship from the helper table to the fact table, so that the helper table filters the fact table. Then you can use the category from the helper table in visuals, and the measure is a simple SUM of the fact table count column.

Re: DAX Count() with some records counted twice

Marpas — Thu, 06 Feb 2025 13:44:59 GMT

<<Table[Category] = "A.1 + B", "B",>> will never be executed

Re: DAX Count() with some records counted twice

bhanu_gautam — Thu, 06 Feb 2025 16:06:39 GMT

Marpas , This is not sum this is column name as mentioned in your data

A.1 + B

Re: DAX Count() with some records counted twice

Marpas — Thu, 06 Feb 2025 16:50:43 GMT

Yes, that's what worked the best. I've tried various combinations and at the end helper table was the way to go.

For future reference: There were more niuanses than I've mentioned in intial question.
Count field doesnt exists, is calculated on the fly by counting unique ItemIDs in fact table.
There is another table, let's call it estimation table (coming from different source).
Goal was to calculate fact/estimated ratio per category.

to simply show numbers, creating relationships was sufficient.
But to calculate ratio, I had to create measures in helper table.

CountFact =

DISTINCTCOUNT(factTable[ItemID])

CountEstimated =

DISTINCTCOUNT(estimatedTable[ItemID])

Ratio = [CountFact] / [CountEstimated]

Re: DAX Count() with some records counted twice

vivek31 — Thu, 06 Feb 2025 17:08:52 GMT

HI Marpas

you can use this Dax measure

Total = var a_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.1") + CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.2") + CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.1 + B") var b_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "B") + CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.1 + B") var c_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "C") var d_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "D") var e_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "E") var f_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "F") RETURN SWITCH(SELECTEDVALUE(Table_[Category ]), "A",a_total, "B",b_total, "C",c_total, "D",d_total, "E",e_total, "F",f_total)