topic Show actual counts and 0 values in calculated table column (to identify the gap in products) in DAX Commands and Tips

Show actual counts and 0 values in calculated table column (to identify the gap in products)

PatrickNahayo — Mon, 17 Jun 2024 13:18:13 GMT

i have the following table where we have the possibilities for the same ID to be assigned a grade multiple times in the dataset.

id	grade (A,B,C,D)	count
1	A	1
1	B	2
1	C	2
2	C	1
2	D	1
2	B	1

However looking at this example, i need to show that there are some gaps, so that we can introduce product recommendations.

I need to create a calculated table or a column that will show me the nulls or 0 values as well in each category where we don't have a product count for example 1D has nothing, and 2A.

id+grade	count (with 0 or nulls)
1A	1
1B	2
1C	2
1D	0
2A	0
2B	1
2C	1
2D	1

I have tried the following measures but is not working in a calculated column:

CountID =

var vCountGrades =

CALCULATE(

COUNTROWS('Products'),

FILTER(

'Products',

'Products'[ID] = EARLIER('Products'[ID]) &&

'Products'[grade]= EARLIER('Products'[grade])

)

RETURN

IF(ISBLANK(vCountGrades ), 0, vCountGrades )

Re: Show actual counts and 0 values in calculated table column (to identify the gap in products)

Greg_Deckler — Mon, 17 Jun 2024 13:31:57 GMT

PatrickNahayo Try this as a new calculated table:

Table 2 = ADDCOLUMNS( GENERATE( DISTINCT('Table'[id]), DISTINCT('Table'[grade]) ), "count", VAR __id = [id] VAR __grade = [grade] VAR __result = MAXX( FILTER('Table', [id] = __id && [grade] = __grade ), [count] ) + 0 RETURN __result )

Re: Show actual counts and 0 values in calculated table column (to identify the gap in products)

Anonymous — Tue, 18 Jun 2024 06:30:22 GMT

Hi PatrickNahayo

Your solution is great, Greg_Deckler . Here I have another idea in mind, and I would like to share it for reference.

PatrickNahayo , you can create a calculated table as follows.

CalculatedTable = VAR _IDList = DISTINCT('Table'[id]) VAR _GradeList = DISTINCT('Table'[grade]) VAR _AllCombinations = CROSSJOIN(_IDList, _GradeList) RETURN ADDCOLUMNS( _AllCombinations, "count", COALESCE([countMeasure], 0) )

The reason I created [countMeasure] is that I don't know how your count expression is calculated, I created that formula just to get an expression because the COALESCE formula requires an expression as its argument. You can replace it with your [count] expression.

Output:

For more details, you can read related document link:

COALESCE function (DAX) - DAX | Microsoft Learn

Best Regards,
Yulia Xu

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

Re: Show actual counts and 0 values in calculated table column (to identify the gap in products)

PatrickNahayo — Wed, 26 Jun 2024 14:31:17 GMT

Thanks very much for your inputs and solutions that have worked great Greg_Deckler Anonymous
However i need to solve an additional analytics task on top of this to recommend which products is the best, i have tried with multiple IFs and SWITCH statements but with no satisfying results.
Basically i need to compare for each product which highest grade has the highest number in each category and mark that with an X so that i can compare with what doesn't have.

		A=high, D=low	A=high, C=low
id	category	grade 1	grade 2	qty grade 1	qty grade 2	recommend
1	1	A	C	1	3
2	1	B	A	2	4	x
3	1	C	B	4	5
4	1	D	A	0	2
5	2	A	B	2	3
6	2	A	A	2	2	x
7	2	C	B	1	3
8	2	D	C	1	2

Thanks very much again for your assistance on this matter