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 ChampionHaving 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.
Helper IIIHi,
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:
- MattAllington
I think you are confusing Power Query (shaping) and Power Pivot (Modelling). New Table is power Pivot, List.Count is POwer query
http://exceleratorbi.com.au/shaping-modelling-power-bi/
- ImkeF
Hey Matt MattAllington - congrats to your 1000 st post :-)
- MattAllington
Oh wow, I new it was coming but didn't notice it tick over. I guess I just ruined it with this reply!
