Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone! I have a measure, see below, - but it is not reporting counts correctly.
ProjectRollUp = var t=SUMMARIZE(filter(FullTable,FullTable[ProjectIsVendorSingleDOS]=TRUE),'FullTable'[Project],"ChartRollUp",DISTINCTCOUNT(FullTable[RollUpID]))
return
SUMX(t,[ChartRollUp])
In this situation, some lines may show several results over the course of a few days. Example below.
Project | Date | RollUpID |
A | 6/1/21 | abc123 |
A | 6/1/21 | def456 |
A | 6/3/21 | abc123 |
So in my measure I want to see that on 6/3/21 - 'abc123' happened and ignore the 'abc123', 6/1/21 line. But I still want the 'def456' 6/1/21. The issue is when the RollUpID exists more than once (like with the 'abc123') I need to assign it in my matrix under the most recent date, 6/3/21. Hopefully this makes sense... TIA.
The assumption is that RollupID's are unique between projects, i.e., one project can't have the same RollupID's as another.
RollupID Count =
var CurrentMaxDate = MAX( T[Date] )
var CurrentRollupIDs = DISTINCT( T[RollupID] )
var CountRollupIDs =
COUNTROWS(
EXCEPT(
CurrentRollupIDs,
CALCULATETABLE(
DISTINCT( T[RollupID] ),
KEEPFILTERS( T[Date] > CurrentMaxDate ),
ALLSELECTED( T[Date] )
)
)
)
return
CountRollupIDs
ProjectRollUp =
CALCULATE(
SUMX(
DISTINCT( T[Project] ),
CALCULATE( DISTINCTCOUNT( T[RollUpID] ) )
),
KEEPFILTERS( T[ProjectIsVendorSingleDOS] )
)
// or (both should return the same result
// but one could be faster than the other)
ProjectRollUp =
CALCULATE(
SUMX(
ADDCOLUMNS(
// You should NEVER use SUMMARIZE
// for anything else but grouping.
// The SUMMARIZE function is so
// complex that if you use it for
// anything more, you are risking
// calculating wrong figures before
// you know it and you WON'T be able
// to even spot it. Here's the article
// that you should read:
// https://www.sqlbi.com/articles/all-the-secrets-of-summarize/
// And here's a horror story about
// single-table models, so stay away
// from them if you want to avoid
// scratching your head for weeks
// or even produce wrong number without
// knowing it:
// https://www.sqlbi.com/tv/auto-exist-on-clusters-or-numbers-unplugged-22/
SUMMARIZE(
T,
T[Project]
),
[@DistinctCount]
CALCULATE(
DISTINCTCOUNT( T[RollUpID] )
)
),
[@DistinctCount]
),
KEEPFILTERS( T[ProjectIsVendorSingleDOS] )
)
Mandatory:
1. https://www.sqlbi.com/articles/all-the-secrets-of-summarize/
2. https://www.sqlbi.com/tv/auto-exist-on-clusters-or-numbers-unplugged-22/
This doesn't solve for the case where the RollUpID exists more than once (like with the 'abc123') I need to assign it in my matrix under the most recent date, 6/3/21 - I am getting the same results with your solution as my own using SUMMARIZE. I think I need to incorporate a conditional that says 'if this ID exists use max(date), or something like that.
You need to explain what you really want in a much clearer way, I'm afraid. My formula stems from my understanding. If I got it wrong, it's because the explanation is not clear enough.
Project | Date | RollUpID |
A | 6/1/21 | abc123 |
A | 6/1/21 | def456 |
A | 6/3/21 | abc123 |
see below desired results, the counts of one per day is the distinct count of the RollUpID per day - only the lines in red (above) would be counted because the ID 'abc123' defaults to the max(date).
Project | 6/1/21 - | 6/3/21 |
A | 1 | 1 |
If you refer back to the original example above. If I put this into a Matrix visual with the Date as my columns field, the RollUpID 'abc123' is shown twice. Once under 6/1/21 and again under 6/3/21. I do not want to aknowledge/ count the line for 6/1/21, where RollUpID is 'abc123'. So I think I need to add a condition somewhere that will default to the MAX(Date) when the Project and RollUpID appear more than once.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |