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