Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |