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

[PatrickNahayo]

Hi

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 )

[Greg_Deckler · Accepted solution]

@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
)

[PatrickNahayo]

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

[Anonymous · Accepted solution]

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.

[Greg_Deckler · Accepted solution]

@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
)