Converting an Array formula from excel to dax

[LEO4929]

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","")}

[LEO4929]

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 · Accepted solution]

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]

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

[dax]

Hi @LEO4929 , 

Did you use Excel to connect other datasource? If so, you could use code in this properties(Click Data->Show Queries, then click table in workbook queries, and click advanced editor, use code in it)

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.