Forum Discussion
Error using SUMMARIZE() to filter out rows
Sorry for the newbie question:
1. I have a table that looks like columns 1 and 2 below, and I want to transform it into columns 3 and 4.
| IssueNr - | CountriesImpacted - | IssueNr - | CountriesImpacted |
| 100 | NL | 100 | NL |
| 101 | FR | 101 | GLOBAL |
| 101 | GLOBAL | 101 | GLOBAL |
| 102 | FR | 102 | FR |
| 103 | IT | 103 | IT |
| 104 | ES | 104 | ES |
| 104 | FR | 104 | FR |
Explanation:
An Issue can impact multiple Countries, e.g. Issue 100 impacts NL, and Issue 104 impacts ES and FR.
However, Issue 101 impacts FR and GLOBAL which is redundant, since GLOBAL clearly implies impact on all countries, hence I want to remove the row corresponding to FR for that Issue.
My Failed Attempt:
I want to create a new calculated table where any occurrences of GLOBAL + Country are replaced with just GLOBAL.
I am trying the following DAX but I do not know how to 'include the current row' in the false part of the IF() statement, since there are multiple CountriesImpacted.
DEFINE
VAR _A = SELECTCOLUMNS(
FILTER(ISSUE, NOT(ISSUE[CountriesImpacted] IN {"NULL"})),
"IssueId",ISSUE[ID_ISSUE],
"OriginalImpacted",ISSUE[CountriesImpacted]
)
VAR _B = SUMMARIZE(
FILTER(ISSUE, NOT(ISSUE[CountriesImpacted] IN {"NULL"})),
ISSUE[ID_ISSUE], // group by IssueId
//ISSUE[CountriesImpacted], -- adding this line does not work as it groups by [IssueId] and [CountriesImpacted]
"IssueId",ISSUE[ID_ISSUE],
"CountriesImpacted",
IF (
FIND("GLOBAL", CONCATENATEX(VALUES (ISSUE[CountriesImpacted] ),ISSUE[CountriesImpacted], ", "), 1, 0) <> 0,
"GLOBAL",
ISSUE[CountriesImpacted] // DAX error since CountriesImpacted has multiple rows per Issue
)
)
VAR _C = DISTINCT(NATURALLEFTOUTERJOIN(_A, _B)) // joining tables so I can compare input vs output
EVALUATE _C ORDER BY [IssueId]
Have been at this for several days now, but I cannot see the light.
Q. How do I filter out Issues having both a Country and GLOBAL into a new table that eliminates the redundant Country row?
Thanks in advance
Hi quijote,
Thank you for the update. We appreciate the effort you’ve put into exploring different solutions and developing your own approach. We understand that unique scenarios like the "GLOBAL + Country" case often require customized logic, especially with complex filtering needs. While our suggestions may not have fully addressed your requirements, we are pleased to see you are testing your own workaround.
In parallel, we’ll revisit this scenario on our end to see if we can provide a more precise solution that meets your need to filter out redundant country rows when "GLOBAL" is present for an issue.
If your custom approach works well, we’d appreciate it if you could share it with the community, as it may help others with similar data challenges.
Thank you.
9 Replies
- bhanu_gautam
Super User
quijote , Try using
NewTable =
VAR IssuesWithGlobal =
FILTER(
ADDCOLUMNS(
SUMMARIZE(ISSUE, ISSUE[ID_ISSUE], ISSUE[CountriesImpacted]),
"HasGlobal", CALCULATE(COUNTROWS(FILTER(ISSUE, ISSUE[CountriesImpacted] = "GLOBAL")))
),
[HasGlobal] > 0
)VAR FilteredIssues =
EXCEPT(
SUMMARIZE(ISSUE, ISSUE[ID_ISSUE], ISSUE[CountriesImpacted]),
FILTER(IssuesWithGlobal, ISSUE[CountriesImpacted] <> "GLOBAL")
)RETURN
FilteredIssues Thanks, however DAX gives the following error:
Resolve the error to see results
Query (19, 2) Each table argument of 'EXCEPT' must have the same number of columns.
BmejiaCreate Two Column one that identifies the IssueNr that have Global, then another column that replaces anything that includes Global and country as global.
MaxGlobal =If(ISSUE[CountriesImpacted - ]="GLOBAL",1,0)
CountriesImpacted =VAR NEW = CALCULATE(MAX(ISSUE[MaxGlobal]),ALLEXCEPT(ISSUE,ISSUE[IssueNr - ]))RETURNIF(NEW>=1,"GLOBAL",ISSUE[CountriesImpacted - ])- Ashish_Mathur
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Grouped Rows" = Table.Group(Source, {"IssueNr"}, {{"Count", each _}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows([Count], each [CountriesImpacted]<>"GLOBAL"))[[IssueNr],[Custom]], #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"CountriesImpacted"}, {"CountriesImpacted"}) in #"Expanded Custom"Hope this helps.
- Aburar_123
Solution Supplier
Hi quijote ,
Please try this measure.
Countries Impacted Calc =var Global_Issues = FILTER(ALL('Table'),'Table'[Countries Impacted]="GLOBAL")VAR Global_Issues_Nr = SELECTCOLUMNS(Global_Issues,"Issue No.",'Table'[Issue Nr])return if(MAX('Table'[Issue Nr]) in Global_Issues_Nr,"GLOBAL",MAX('Table'[Countries Impacted])) - v-sgandrathi
Community Support
Hi quijote,
I wanted to check if you had the opportunity to review the information provided by our super users.
Please feel free to contact us if you have any further questions.
Thank you and continue using Microsoft Fabric Community Forum.
- v-sgandrathi
Hi quijote,
I wanted to check in your situation regarding the issue. Have you resolved it? Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!Thanks for reaching out. None of the solutions provided fulfilled my exact requirements, but I did manage to devise my own solution, which is under test right now. Therefore, I cannot accept any of the replies as the official solution on this occasion.
