Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Running Count based on Data Version, Product, and Request Date

Hello Everyone, I am new to all this and getting pretty twisted up in what I think should be an easy solution after weeks of trying to figure it out for myself on this community forum and other sour...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous My bad, that's what I get for not setting up the model and testing properly. I believe this should do it and PBIX is attached below signature. Thanks for sharing the PBIX, really helps.

    Count Req Date Column = 
  VAR __Product = [Product]
  VAR __ReqDate = [Req Date]
  VAR __Date = [Data Version]
  VAR __MinDate = MINX(FILTER(ALL(Table1),[Product] = __Product && [Req Date] = __ReqDate),[Data Version])
  VAR __Result = ( __Date - __MinDate ) * 1. + 1
RETURN
  __Result
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Greg_Deckler , thanks for the fast response!  

 

I gave it a try, must be doing something wrong.  Below is a link to a PBI file with the data and the Measure as well as trying it as a calculated column.  When I did a measure, it didn't let me set the VAR as a reference to the table, so I had to use a SELECTVALUE measure.

I am thinking this was supposed to be a calculated column, so I did that as well.  But I am not getting the expected results.  Thoughts?

https://drive.google.com/file/d/1zp3zOvDQI7qTCQIhngVblnwQT7W_DFgq/view?usp=share_link 

 

I really like the MTBF example and will play with that some more, could be really handy in my world as well.  
The Cthulhu approach was pretty complicated, I will give it a look on a smaller set of data. When I tried to apply it to my real data which has >2Million rows, it crashed, likely user error:-)

Thanks again!

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous My bad, that's what I get for not setting up the model and testing properly. I believe this should do it and PBIX is attached below signature. Thanks for sharing the PBIX, really helps.

Count Req Date Column = 
  VAR __Product = [Product]
  VAR __ReqDate = [Req Date]
  VAR __Date = [Data Version]
  VAR __MinDate = MINX(FILTER(ALL(Table1),[Product] = __Product && [Req Date] = __ReqDate),[Data Version])
  VAR __Result = ( __Date - __MinDate ) * 1. + 1
RETURN
  __Result