Forum Discussion
Counting a Measure
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
TomMartens Unfortunately it timed out saying I dont have enough memory. Its probably becauue I am dealing with 20 million rows.
Hey PowerBI123456 ,
I'm sorry to hear that.
The "final" measure, seems not that different but there is a subtlety, it's in determinig the MaxRequestID, as it now has to find the appropriate ids first (less than the MaxResponseID) before the MaxRequestID can be determined.
This leads to materialization of "queries" as a simple boolean scan is no longer sufficient.
Next to that you just have a "dreaded" one table solution, this is responsible for this part of the measure
...
FILTER(
'Activity'
, CALCULATE(
CONTAINSROW(
VALUES( 'Activity'[Activity] )
, "Response"
)
, ALLEXCEPT( 'Activity' , Activity[Account] )
)
)
...the above determines better omits the Accounts that have no respons. This and some other components can be simplified using a proper star schema.
I recommend reading this article (Design a data model in Power BI - Learn | Microsoft Docs).
Of course the measure can be optimized, but this can become very time consuming and a thorough understanding of your data and and your data model is necessary.
Without having access to the pbix, meaning your data, this will be impossible. Please be aware that personally I will not work with pbix files that are shared with me via private links, or private messages.
I'm afraid that this will exceed the time most of us are able/willing to spent, at least mine.
Regards,
Tom
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
TomMartens Thanks, and totally get it! Thanks for also sending that article. I am going to attempt this one more time since I think my model is in a star schema. Unfortunately, I can't upload the actual model since its confidential. But I have updated the sample file to include the dimension tables I do have in my model. This is obviously a lot more simple since my dimension tables have more columns and my fact table has millions of rows, but it should still work. Hopefully these dimension tables can help improve the measure you created if you don't mind looking one more time. I truly appreciate all your help on this!
Hey PowerBI123456 ,
this is look like a perfect star schema 🙂
Maybe you want to upload another pbix and correct the typo in the activity table
Reponse --> Response
It is never a good idea to simplify your request by providing sample data that does not reflect your data model.I spent some hours to come up with the "final" measure, in vain.
Silly me, that I did not ask if this is really your data model, but I was intrigued by the complexities of your question and got carried away 🙂
Nevertheless, whenever we will meet in real life you owe me a beer 😉Regards,
Tom
TomMartens Thanks, and updated file. Does that mean you will try helping me one more time 🙂
Apologies and lesson learned, I will make sure to upload my whole model. Definitely owe you more than 1 beer!
Yes, I will but, unfortunately I will look at this on the coming weekend, as my mind got distracted by some work related challenges.
Regards,
TomTomMartens No problem. Thank you, thank you, thank you!
Hey PowerBI123456 ,
a question to the nature of the ID in your fact table.
Is this ID an index that creates a sequence growing by 1 without gaps in your fact table
If so, is this true: inside a group of events (defined by an account) the index of an response minus 1 always points to an event of type request?Or can it be possible that the immediate predecessor of an response is another response.?
Regards,
TomTomMartens Hi, its not always minus 1 since other activity can happen between a request and response. But the last request before a response received should be the matching request. So it can look something like the below, I only care about the requests and response, not the other activity. So for account 1, I am looking for Joe's request (ID 3). Does that help? I have uploaded the data to include this if it helps. Sample File
Thanks!
TomMartens Thank you soooo much!!!! You rock!