Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

I have a table as shown below

 

Supplier NameScore LevelDate of Audit
A0January 25, 2022
A0January 30, 2022
A4February 25, 2022
B5March 25, 2022
B5April 25, 2022
C1April 30, 2022
D3February 19, 2022
E2February 17, 2022
F2June 15, 2022
F4May 13, 2022
G3October 3, 2022
G4March 24, 2022
G4April 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 LevelCount Of Supplier
00
11
21
31
43
51

 

Thanks for helping me out

1 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

 

View solution in original post

6 REPLIES 6
daXtreme
Solution Sage
Solution Sage

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

 

I tried above solution however it gives below error

nripendrasinha_0-1659111652934.png

nripendrasinha_1-1659111694268.png

 

 

 

[# 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

 

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.

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

@daXtreme Noted. Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors