Forum Discussion

majdkaid22's avatar
majdkaid22
Helper V
9 years ago
Solved

Is Group changed using If

Hi guys,

 

I have the below data, recording every change happen on trading account concerning the risk model, which is the represented in the (GroupCode)

 

Every change is recorded, and several changes can be found there for 1 single account. 

'

 

 

I need a formula to highlight the trading accounts that had a change in the their group, and precisly the last charerter is what my concern, as it's either S or X

 

I dont need to know how many times a change has happen, I just need to shortlist the accounts that had change ( at least once) and accounts that never had a change. 

 

 

Appreciate any help!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    OK, this is a bit brute force, but I created an Enter Data query:

     

    GroupCodes

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg120XX01Q12c/EM0g1W0lEyNNA3MtE3MjA0U4rVwSFvTEDeiIC8IQF5AxzyERB5Q0sC8haY8iHBAQZweXMs+j2dDI1gDjA0w6UAZoIpFhuQXYAtBFEMwBaEKE6AhWEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [GroupCode = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GroupCode", type text}, {"Date", type date}})
in
    #"Changed Type"

    I then created these custom columns in DAX:

    Group = LEFT([GroupCode],LEN([GroupCode])-2)

Code = RIGHT([GroupCode],1)

HasX = IF([Code]="X",1,0)

HasS = IF([Code]="S",1,0)

    Then you can put Group, HasX and HasS in a table visual and potentially filter by 0's to get whatever combination that you want.

     

     

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Not entirely sure exactly what you are trying to do but a couple of things. First, if you want the last character, you could create a column like:

     

    Column = RIGHT([GroupCode]],1)

    You could also create a measure like:

     

    Measure = VAR datemax = MAX([Date])
VAR datemin = MIN([Date])
RETURN (datemax - datemin) * 1.

    Then just filter out the 0's and you have the accounts that have changed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi majdkaid22,

     

    >>I need a formula to highlight the trading accounts that had a change in the their group, and precisly the last charerter is what my concern, as it's either S or X

    I agree with smoupre's point of view, you can use right function to check the last charerter. Below is the sample:

     

    Measure:

    Changed = if(COUNTROWS(FILTER(ALL(Sheet1),Sheet1[Account Number]=MAX(Sheet1[Account Number])&&RIGHT(Sheet1[GroupCode],1)<>RIGHT(LASTNONBLANK(Sheet1[GroupType],[GroupType]),1)))>0,TRUE(),FALSE())

     

    Calculate table about accounts list:

    Table = DISTINCT(SELECTCOLUMNS(Sheet1,"Account Number",[Account Number],"IsChanged",[Changed]))

     

    Regards,

    Xiaoxin Sheng

    • majdkaid22's avatar
      majdkaid22
      Helper V
      9 years ago

      Greg_Deckler & Anonymous Thanks for your feedback.

       

      Am not sure I made myself clear in what exactly is needed. 

       

      You can see in the below example of Acount Number 442197 there were many changes happen on the GroupCode throughout the time from a group ends with S to a group ends with X, and vicse versa. 

       

      This is the type of accounts that I would like to filter, where in the case of this account, it falls under IsChanged "TRUE," while if all the groupcode changes happened within Groupcodes end with S, it fall under ,IsChanged, "FALSE"

       

       

      I don't think the proposed formulas would actually achieve what am looking for? 

       

      The formula has captured the below as "True" while it should have been "FALSE" as the account always remianed under groupcode ends with S

       

       

      Hope you could help me further :)

       

      Cheers,

      Majd

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        9 years ago

        OK, just so I have this straight. GroupCodes start with having an S extension. Changes happen and they continue to have the S extension. Then, for some GroupCodes, they end or something and at that point they are given an X extension. You want to identify the ones that have a change from S to X and those that never change and have all S entries and no X entries. Is that correct?