Forum Discussion
Count columns where value is not {null, blank, 0}, filtered on column name by condition
Hi,
i have a dynamic number of columns in one table, with metrics calculated for countries.
Each country is concatenated with the metrics:
| product | USA mins | CAN mins | SWE mins | DEN mins | USA profit | CAN profit | SWE profit | DEN profit |
| car | 1 | null | (blank) | 0 | 200 | 200 | 200 | 200 |
| bike | 1 | 1 | 0 | null | 200 | 200 | 200 | 200 |
I need a column for a specific metric, say mins, that would count the number of columns where the value is not null, zero or blank in this scenario.
The current issue is that I do not know what countries will be included in the data when I refresh it.
it would be a combination of the below 2 calculations, but I cannot figure out how to add them together.
List.Count(List.RemoveNulls(List.Difference(List.Range(Record.FieldValues(_),2), {null, "", " ", 0})))
List.Sum(List.Transform(List.Select(Record.FieldNames(_), each Text.EndsWith(_, "mins")), (name) => Record.Field(_, name)))
Any help is appreciated.
Thanks
Peter
1 Reply
- AnonymousNot applicable
Hi peppelino ,
Is it possible to consider selecting the [product] column, and then unpivot the other columns, before proceeding to the next calculation? This way, even if the column is dynamic, it is still easy to calculate.Calculate the results and then pivot back to the original table layout.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum