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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi  PowerBI123456  ,

Here are the steps you can follow：

1. Enter power query through transform data and select add column --- index column --- from 1 to generate the index

2. Create calculated column.

Flag =
var _1=
CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),[Activity]="Request"&&[Account]=EARLIER('Table'[Account])))
var _2=
CALCULATE(MAX('Table'[Activity]),FILTER('Table',[Account]=EARLIER('Table'[Account])&&[Index]=EARLIER('Table'[Index])+1))
return
IF(
    _1=[Index]&&_2="Response",1,0)

3. Create measure.

count = SUMX(ALL('Table'),[Flag])

4. Result：

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.