Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago
Solved

Counting a Measure

Updated with sample file: Sample File    Hi, hope someone can help!   I have the data below and using the measure below to show me the ID of the most recent "Request" if there was a "Response" by...
Need Help
Tips and Tricks
  • TomMartens's avatar
    TomMartens
    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

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago

TomMartens Hi - I am trying to show the users who put in the last request. Based on the data, it would be:

  • Account 1: John
  • Account 2: Will
  • Account 3: Amber
  • Account 4: John

I want to use a treemap to show that each of those 4 people had 1 request. The problem is my current treemap is counting everyone that made a request for each account, so its showing more than I want. Hope that makes sense. 

TomMartens's avatar
TomMartens
Super User
5 years ago

Hey PowerBI123456 ,

 

this measure

MaxReq = 
var t = 
    ADDCOLUMNS(
        FILTER(
            'Activity'
            , CALCULATE(
                CONTAINSROW(
                    VALUES( 'Activity'[Activity] ) 
                    , "Response" 
                ) 
                , ALLEXCEPT( 'Activity' , Activity[Account] )
            )
        )
        , "MaxRequestID"
            , var account = [Account] 
            var maxID =
                CALCULATE( 
                    MAX( Activity[ID] ) 
                    , ALL( 'Activity' )
                    , 'Activity'[Account] = account
                    , 'Activity'[Activity] = "Request"
                )
            return
            maxID
    )
return

SUMX(
    t 
    , IF(
        'Activity'[ID] = [MaxRequestID] && 'Activity'[Activity] = "Request"
        , 1 --[MaxRequestID]
        , BLANK()
    )
)

allows to create this report page

From a data visualization point of view I favor the stacked bar much more then the Tree map.

If "just" want to show the user who is in charge for the last request, I would use a simple table Account | User | the measure [MaxReQ]
I'm not sure if I really understand the date stuff from your measure, maybe you have to check my measure with a larger dataset.

 

Hopefully, this is what you are looking for.

Regards,
Tom


  • PowerBI123456's avatar
    PowerBI123456
    Post Partisan
    5 years ago

    Thank you soo much!!! This works, but the only thing it is getting the max ID of the overall request per account, but I need the Max ID of the request right before the response. I updated the sample file to show what I mean and apologies for not adding it earlier. There could be another request after a response which this measure would catch. I need the request right before the response. I added another request for account 4 after the response by Jack. This measure is catching that request when it should be John. Hope that makes sense. 

     

    Updated file:  Sample File 

    • TomMartens's avatar
      TomMartens
      Super User
      5 years ago

      Hey PowerBI123456 , to rephrase this requirement a little diffrently ...
      You are looking for the MAX Request-ID that is smaller then the LARGEST Response-ID for the same account, correct?
      Regards,
      Tom