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

Greg_Deckler appreciate you taking the time to demonstrate the case for me!

 

I know very basic info when it comes to data query. 

 

I opened up my Advanced Editor and tried to paste your proposed Data query, but am keep getting an error. 

 

Obviously am not writing the query the correct way. 

 

 

Below is my current query for meAccountHistory table.  

 

How should I add your query to it?

 

let
    Source = Sql.Databases("102.222.13.15"),
    ODS = Source{[Name="ODS"]}[Data],
    dbo_meAccountHistory = ODS{[Schema="dbo",Item="meAccountHistory"]}[Data],
    #"Replaced Value" = Table.ReplaceValue(dbo_meAccountHistory,null,"0",Replacer.ReplaceValue,{"AgentAccountNumber"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Unknown",Replacer.ReplaceValue,{"AgentAccountName"})
in
    #"Replaced Value1"

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi majdkaid22,

 

You shouldn't add smoupre's query, it means these data is load from excel(your data is load from sql). You need to create calculate columns and create the visual.

 

Regards,

Xiaoxin Sheng

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