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 c...
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.

     

     

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?

    • majdkaid22's avatar
      majdkaid22
      Helper V
      9 years ago

      Greg_Deckler That is very much true Sir!

       

      I want to identify the ones that have a change from S to X, or vice versa, and those that never change and have all S entiries or X entiries

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        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.