Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi Community,
I ask your help in something that seems pretty simple, but I´m unable to find an answer.
I want to calculate, in a card visualization, the nº of different values that exist on a particular column in my dataset.
The particularity of this column ("Column1") is that accepts multiple values, separated by a comma. For example, consider the simple table below.
In this particular case, the result should be 11. i want to do it without having to create a new table or modify my current one.
I´m itrying to combine the following steps, without success:
1º create a table variable with only 1 column and contains only the different values of Column1 (Addcolumns+Summarize)
2º in the new table, add new rows for each different value on each row (Generateseries?)
3º count the distinct number of rows
Im very confused right now and I dont know how to use the column of my new table variable inthe distinctcount
Please help
thank you in advance
54 |
216,549 |
992,97,769,1125,1127 |
823 |
96 |
823 |
93 |
Solved! Go to Solution.
Hi Sahir,
thank you for your help!
It didnt quite return the correct values in my original dataset. Is there any limitation in the number of characters a row can have? because the returned value is lower than the correct one which make me think that probably is not able to cover all characters.
It also returned a minor error: "Function "addcolumns" cannot add column [Value] since it already exists".
I made a small change that was accepted:
Hello @Joao_Marto,
Can you please try this DAX:
Distinct Value Count =
VAR Separator = ","
VAR ExpandedTable =
GENERATE(
ALL('YourTableName'),
VAR CurrentValues = [Column1]
VAR ValueList = UNICHAR(10) & SUBSTITUTE(CurrentValues, Separator, UNICHAR(10)) & UNICHAR(10)
RETURN
GENERATESERIES(1, LEN(ValueList) - LEN(SUBSTITUTE(ValueList, UNICHAR(10), "")), 1)
)
VAR ResultTable =
ADDCOLUMNS(
ExpandedTable,
"Value",
TRIM(MID(SUBSTITUTE([Column1], Separator, REPT(" ", 100)), ([Value] - 1) * 100 + 1, 100))
)
RETURN
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
ResultTable,
"TrimmedValue", [Value]
)
)
)
Hi Sahir,
thank you for your help!
It didnt quite return the correct values in my original dataset. Is there any limitation in the number of characters a row can have? because the returned value is lower than the correct one which make me think that probably is not able to cover all characters.
It also returned a minor error: "Function "addcolumns" cannot add column [Value] since it already exists".
I made a small change that was accepted:
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
10 | |
10 | |
10 | |
10 |
User | Count |
---|---|
19 | |
14 | |
13 | |
11 | |
8 |