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.

     

     

majdkaid22's avatar
majdkaid22
Helper V
9 years ago

Anonymous I did that first thing, but am getting the following error for the below column when I try to create it

 

"An argument of function 'LEFT' has the wrong data type or has an invalid value"

 

 

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

 

I assume it's the data type of [GroupCode], but I cannot know what to change it to.

 

Thanks,

Majd

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

I believe you need GroupCode to be Text for LEFT to work. You can change the data type in either the query editor or in your DAX model.