cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Converting an Array formula from excel to dax

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.

View solution in original post

4 REPLIES 4
Highlighted
Regular Visitor

Re: Converting an Array formula from excel to dax

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

Current Result.JPGDesired Result.JPG

Highlighted
Community Support
Community Support

Re: Converting an Array formula from excel to dax

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.

View solution in original post

Highlighted
Regular Visitor

Re: Converting an Array formula from excel to dax

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

Highlighted
Community Support
Community Support

Re: Converting an Array formula from excel to 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)

596.PNG

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.

 

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors