cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

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

1 ACCEPTED SOLUTION
Solution Sage

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

6 REPLIES 6
Solution Sage

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``````

Helper I

I tried above solution however it gives below error

Solution Sage

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

Helper I

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.

Solution Sage

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

Helper I

@daXtreme Noted. Thanks

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors