Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
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
[# 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.
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...
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
41 | |
30 | |
25 | |
23 | |
23 |