Forum Discussion
Helper IIIappend data from two columns only for distinct count.
Thank you Imke for your patience and assistance. I was finally able to get it using the old copy/paste/paste/merge/remove duplicates way. I was able to get the chart right too - apparently I had the relationship pointing the wrong way. Once I changed the direction, it is now displaying the right values.
Community ChampionYou can take this simple formula to do this:
List.Count(List.Distinct(List.Union(Table.ToColumns(Source))))
Where "Source" is the reference to the table to be analyzed.
It reads inside-out as in Excel:
Table.ToColumns: transfers every column of your table to a list and then combines all those lists into a big list (nested list of lists).
List.Union: combines them
List.Distinct: removes dups
List.Count: does what it says ;-)
This is case-sensitive. Pls let me know if you want a case-insensitive count instead.
Helper IIIThank you for the reply,
With this being just a number - where would this be created? It doesn't appear to be a new column since just a number, but when I try to create a new measure, it won't let me select any tables as the source, only existing measures I've already created.
I tried doing this formula too, based on a post from your earlier for someone else,
List.Distinct(List.Union([CI Identifier], [Child CI Identifier]))
with CI Identifier and Child CI Identifier being the two columns I'm trying to merge and I was going to just then do a count on them, but I get this error:
Expression.Error: We cannot convert the value "CI00000085018" to type List.
Details:
Value=CI00000085018
Type=Type
In every row. the CI######## are the values within the rows of data.
- ImkeF
Having difficulties understanding your request.
My formula actually returns just a single value (the number of distinct values in the whole table) and would be loaded as a table with one column and one row to your data model where it could be adressed by any DAX-function.
Do you need the number of distinct values in just some specific columns?
Or sth totally different? If so, pls post a clear picture of input-data and desired output-data.
- Lenihan
Hi,
Sorry didn't mean to confuse. The solution you provided sounds like what I want. Since I was having challenges, i was trying to do it other ways too
So I clicked on New Table, and entered this:
Table = List.Count(List.Distinct(List.Union(Table.ToColumns('CI Relationship Report'))))But I got this error:
The syntax for 'Count' is incorrect. (DAX(List.Count(List.Distinct(List.Union(Table.ToColumns('CI Relationship Report')))))).
- ImkeF
No prob :-)
You need to use this in the query-editor and not in the data model/tab.
Open the query editor -> move your mouse over the query-name in the left navigation pane (Queries...) -> rightclick mouse -> check "reference":
Then a new query will be created with one step in it: "Source" with the reference to your table. Click on the fx like shown in the picture will create a new step where you paste the formula: