Distinct Count of Suppliers based on Score Level on last date of Audit

[nripendrasinha]

I have a table as shown below

Supplier Name	Score Level	Date of Audit
A	0	January 25, 2022
A	0	January 30, 2022
A	4	February 25, 2022
B	5	March 25, 2022
B	5	April 25, 2022
C	1	April 30, 2022
D	3	February 19, 2022
E	2	February 17, 2022
F	2	June 15, 2022
F	4	May 13, 2022
G	3	October 3, 2022
G	4	March 24, 2022
G	4	April 22, 2022

I would like to distinct count the number of Suppliers at each Score level that each supplier scored on their Last Audit date.

My expected output of the visualization is shown below:

However, I am not sure how to First Group with Supplier name, and then only take the Score of their last Audit date.

And then finally count the distinct number of Suppliers based on each Score Level.

Score Level	Count Of Supplier
0	0
1	1
2	1
3	1
4	3
5	1

Thanks for helping me out

[daXtreme]

Hi @nripendrasinha 

First, you have to create a table that will hold the so-called "score on last audit." So, it'll be a simple 1-column dim table with all the scores possible (0-5?). Let's call the table "Last Audit Score." This table will be disconnected. Then you can create a measure that will count the number of suppliers (all I write here refers to THE CURRENT CONTEXT) that have a particular score (taken from the new dim table) on their last audit date (as seen in the current context). Such a measure could look like this:

[# Suppliers] =
// LAScore = Last Audit Score
var VisibleLastAuditScores = VALUES( 'Last Audit Scores'[LAScore] )
// You also have to have a dimension date table which you can
// connect via one-to-many to your fact table on the Date Of Audit
// field. And please remember that slicing should always be done
// by dimensions, never via fact tables.
var LastVisibleDate = MAX( Dates[Date] )
// These will be suppliers as seen in the current context
// that have their last audit score in the set of the visible
// last audit scores.
var SuppliersOfInterest =
    GENERATE(
        VALUES( YourFactTable[Supplier] ),
        // The assumption is that for each day and
        // each supplier there can only be at most 1 
        // audit since the below must return at most
        // 1 row.
        FILTER(
            CALCULATETABLE(
                TOPN(1,
                    YourFactTable,
                    YoourFactTable[Date Of Audit],
                    Desc
                )
            ),
            YourFactTable[Score Level] IN VisibleLastAuditScores
        )
    )
var CountOfSuppliers = COUNTROWS( SupplierOfInterest )
return
    CountOfSuppliers

[nripendrasinha]

I tried above solution however it gives below error

[daXtreme · Accepted solution]

[# Suppliers] =
// LAScore = Last Audit Score
var VisibleLastAuditScores = VALUES( 'Last Audit Scores'[LAScore] )
// You also have to have a dimension date table which you can
// connect via one-to-many to your fact table on the Date Of Audit
// field. And please remember that slicing should always be done
// by dimensions, never via fact tables.
var LastVisibleDate = MAX( Dates[Date] )
// These will be suppliers as seen in the current context
// that have their last audit score in the set of the visible
// last audit scores.
var SuppliersOfInterest =
    GENERATE(
        VALUES( YourFactTable[Supplier] ),
        // The assumption is that for each day and
        // each supplier there can only be at most 1 
        // audit since the below must return at most
        // 1 row.
        FILTER(
            // To get rid of the error, you just have
            // to select a column that's not already
            // in the first table that drives GENERATE.
            SELECTCOLUMNS(
                CALCULATETABLE(
                    TOPN(1,
                        YourFactTable,
                        YoourFactTable[Date Of Audit],
                        Desc
                    )
                ),
                "@ScoreLevel", YoourFactTable[Score Level]
            ),
            [@ScoreLevel] IN VisibleLastAuditScores
        )
    )
var CountOfSuppliers = COUNTROWS( SuppliersOfInterest )
return
    CountOfSuppliers

[nripendrasinha]

Hi @daXtreme 

Thanks so much for the suggestion it works 🙂

However, I am a bit unsure why we are using "var LastVisible Date", as we are not using it anywhere in the formula.

And if I remove that line from the formula then also it works fine.

If you can clarify then it will be good for my understanding.

Thanks so much for your help to solve the query.

[daXtreme]

@nripendrasinha 

If it's not being used in the code, then it's not necessary. I can't remember why I put it in there. Maybe when I was developing the measure it was needed...

[nripendrasinha]

@daXtreme Noted. Thanks