Forum Discussion

LEO4929's avatar
LEO4929
Regular Visitor
6 years ago
Solved

Converting an Array formula from excel to dax

Hi all,

 

I'm new to DAX, and was wondering if anyone could provide some help.

 

I have a query that extract data using odbc into the power query, previously with query updated a table, which contained an array formula to test the whole concatenation of two columns, if the count of the two cancatenated columns was greater than 0 then all rows with that account number would be status marked as "Report".

 

I'm strugglinhg to build the same array functionality in DAX so I can remove the table as the data set is increasing.

 

Any help would be much appreciated

 

 

 

{=IF(SUM(IF((DSP_Data[[#All],[Sales Acc Code]]&DSP_Data[[#All],[Due / Not Due Category]])=([@[Sales Acc Code]]&"Overdue"),1,0))>0,"Report","")}

 

 

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi LEO4929 , 

    According to your description, it seems that if AccRef group has "OverDue", you want to show "report" in this group, right? If so, you could try to use below M code to see whether it work  or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8i9LLXIpTVWK1YGJ+OWXIARc8AnEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AccRef = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AccRef", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AccRef"}, {{"Count", each Text.Combine([Status], ","), type text}, {"all", each _, type table [AccRef=text, Status=text]}}),
    Custom1 = Table.ReplaceValue(#"Grouped Rows", each [Count], each if Text.Contains([Count], "OverDue") then "report" else "", Replacer.ReplaceValue, {"Count"}),
    #"Expanded all" = Table.ExpandTableColumn(Custom1, "all", {"Status"}, {"Status"})
in
    #"Expanded all"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • LEO4929's avatar
    LEO4929
    Regular Visitor
    6 years ago

    I have tried the following but doesn't give me the results required, actual result of thebelow code is highlighted in first picture, however, the desired result is in picture 2.

    =if(calculate(COUNT(Table1[AccRef]), filter(all(Table1[AccRef]),(Table1[AccRef]&Table1[Status])=(Table1[AccRef]&"Overdue")))>0,"Report","")

    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi LEO4929 , 

      According to your description, it seems that if AccRef group has "OverDue", you want to show "report" in this group, right? If so, you could try to use below M code to see whether it work  or not.

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8i9LLXIpTVWK1YGJ+OWXIARc8AnEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AccRef = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AccRef", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AccRef"}, {{"Count", each Text.Combine([Status], ","), type text}, {"all", each _, type table [AccRef=text, Status=text]}}),
    Custom1 = Table.ReplaceValue(#"Grouped Rows", each [Count], each if Text.Contains([Count], "OverDue") then "report" else "", Replacer.ReplaceValue, {"Count"}),
    #"Expanded all" = Table.ExpandTableColumn(Custom1, "all", {"Status"}, {"Status"})
in
    #"Expanded all"

      Best Regards,
      Zoe Zhi

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • LEO4929's avatar
        LEO4929
        Regular Visitor
        6 years ago

        Thank you for your response, and yes this is exactly the result I am after, however, I'm struggling to put your suggested code somewhere that will work, please could you advise where I need to put this code.

         

        Many thanks