Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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","")}
Solved! Go to 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.
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","")
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.