Forum Discussion
Counting a Measure
- 5 years ago
Hey PowerBI123456 ,
here you will find a new approach :-), be aware that this approach is referencing the columns from your dimension tables. For this it's also necessary to change the visuals as well. Meaning: use columns from the dimension tables instead.
This measure is also not that generic, as it returns the max requestID, so it will return the expected results whenever the acoount colum is used.
If you use the measure on a card visual it will retrun 12 instead of 4. If you need the 4 I recommend using a SUMX or COUNTX in combination with VALUES('...'[account]MaxReq Star = var t = ADDCOLUMNS( VALUES( 'DIM: Accounts'[Account] ) , "maxrequestid" , var MaxResponseID = CALCULATE( MAX( 'FACT: Activity'[ID] ) , ALL('DIM: Date' ) , ALL( 'DIM: Users' ) , ALL( 'FACT: Activity'[ID] ) , 'DIM: Activity'[Activity] = "Response" ) var MaxRequestID = CALCULATE( MAX( 'FACT: Activity'[ID] ) , ALL( 'DIM: Date' ) , ALL( 'DIM: Users' ) , 'DIM: Activity'[Activity] = "Request" --, 'FACT: Activity'[ID] = MaxResponseID - 1 , 'FACT: Activity'[ID] < MaxResponseID ) return MaxRequestID ) return CALCULATE( MAX( 'FACT: Activity'[ID] ) , TREATAS( t , 'DIM: Accounts'[Account] , 'FACT: Activity'[ID] ) )Here is a screen shot that shows the column usage of the tree map visual:
Be aware that the overall challenge we are facing is based on the fact that the datastore (our beloved SSAS Tabular inside Power BI) does not know a sequence data type. Sometimes, here, this makes things hard, the other times it's a plus.
Nevertheless, if this does not work, you might want to read this article, here I present a different approach to tackle the previous value challenge: The previous value - Mincing Data - Gain Insight from Data (minceddata.info)Regards,
Tom
Hey PowerBI123456 ,
no problem, sometimes it takes some time to find a mutual understanding. I use "rephrasing" not because my explanation is better, it's helps to narrow down the requirement.
Nevertheless, here you go:
MaxReq =
var t =
ADDCOLUMNS(
ADDCOLUMNS(
FILTER(
'Activity'
, CALCULATE(
CONTAINSROW(
VALUES( 'Activity'[Activity] )
, "Response"
)
, ALLEXCEPT( 'Activity' , Activity[Account] )
)
)
, "MaxResponseID"
, var account = [Account]
var maxID =
CALCULATE(
MAX( Activity[ID] )
, ALL( 'Activity' )
, 'Activity'[Account] = account
, 'Activity'[Activity] = "Response"
)
return
maxID
)
, "MaxRequestID"
, var account = [Account]
var maxResponseID = [MaxResponseID]
var maxID =
CALCULATE(
MAX( Activity[ID] )
, ALL( 'Activity' )
, 'Activity'[Account] = account
, 'Activity'[Activity] = "Request"
, 'Activity'[ID] < maxResponseID
)
return
maxID
)
return
SUMX(
t
, IF(
'Activity'[ID] = [MaxRequestID] && 'Activity'[Activity] = "Request"
, 1 --[MaxRequestID]
, BLANK()
)
)
I added a MaxResponseID in the virtual table, that then will be used to find the MaxRequestID that is smaller than the MaxResponseID.
Hopefully, this is what you are looking for.
Regards,
Tom
TomMartens Thank you!!! I think it will work, but its still calculating. I am dealing wtih 20 million rows so its running slow. The first measure ran fast. Anyway to make this faster?