Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
hi,
i have a question regarding unique records.
i have the following table:
key | description | release |
111 | item for sale | rel1 |
111 | item for sale | rel1 |
111 | item for sale | rel2 |
111 | item for sale | rel2 |
111 | item missing | rel3 |
222 | 2nd item | rel1 |
222 | 2nd item | rel1 |
222 | 2nd item | rel1 |
222 | name change | rel1 |
222 | name change | rel2 |
i would like to add a row that counts the unique records accoding to column 'Key' and column 'Release'.
the outcome should be:
key | description | release | unique |
111 | item for sale | rel1 | 3 |
111 | item for sale | rel1 | 3 |
111 | item for sale | rel2 | 3 |
111 | item for sale | rel2 | 3 |
111 | item missing | rel3 | 3 |
222 | 2nd item | rel1 | 2 |
222 | 2nd item | rel1 | 2 |
222 | 2nd item | rel1 | 2 |
222 | name change | rel1 | 2 |
222 | name change | rel2 | 2 |
Thanks in advance!
Solved! Go to Solution.
@yuvalpi OKK 🙂 So I didn't understand what you meant.
This is the column you need:
Column 2 =
VAR _current_key = 'Table'[key]
VAR _result =
CALCULATE(
DISTINCTCOUNT( 'Table'[release] ),
REMOVEFILTERS('Table'),
'Table'[key] = _current_key
)
RETURN
_result
@yuvalpi you mean like this?:
Column 2 =
VAR _current_key = 'Table'[key]
VAR _result =
CALCULATE(
DISTINCTCOUNT( 'Table'[release] ),
REMOVEFILTERS('Table'),
'Table'[key] = _current_key,
'Table'[release] <> BLANK()
)
RETURN
_result
@yuvalpi my pleasure 🙂 Will appreciate your kudos :))
Hey, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂
@yuvalpi this is a column that counts the number of rows that has the same key and release:
Column =
VAR _current_key = 'Table'[key]
VAR _current_release = 'Table'[release]
VAR _result =
COUNTROWS(
FILTER(
'Table',
'Table'[key] = _current_key && 'Table'[release] = _current_release
)
)
RETURN
_result
I'm not sure how you got to your result in the example, so maybe I didn't understand what you need.
Hi @SpartaBI
first - thank you for the swift reply!
regarding the solution, I entered into the BI, but I am not sure it is what I needed.
per my example - I wanted to get per row the number of unique values that meet the criteria.
in the example that I sent, if I focus only on Key 111, I can see 3 different releases (rel1, rel2, rel3), hence I wanted to show the number 3 in all the relevant rows.
key | description | release | unique |
111 | item for sale | rel1 | 3 |
111 | item for sale | rel1 | 3 |
111 | item for sale | rel2 | 3 |
111 | item for sale | rel2 | 3 |
111 | item missing | rel3 | 3 |
Thank you again,
@yuvalpi OKK 🙂 So I didn't understand what you meant.
This is the column you need:
Column 2 =
VAR _current_key = 'Table'[key]
VAR _result =
CALCULATE(
DISTINCTCOUNT( 'Table'[release] ),
REMOVEFILTERS('Table'),
'Table'[key] = _current_key
)
RETURN
_result
Hi again,
I have a followup question.
How can I ingore blanks in the Release column? e.g. for this table to count only the rel1 and rel3, but not the blank value as an item.
key | description | release | unique |
111 | item for sale | rel1 | 2 |
111 | item for sale | rel1 | 2 |
111 | item for sale | 2 | |
111 | item for sale | 2 | |
111 | item missing | rel3 | 2 |
thanks again,
@yuvalpi you mean like this?:
Column 2 =
VAR _current_key = 'Table'[key]
VAR _result =
CALCULATE(
DISTINCTCOUNT( 'Table'[release] ),
REMOVEFILTERS('Table'),
'Table'[key] = _current_key,
'Table'[release] <> BLANK()
)
RETURN
_result
@yuvalpi my pleasure :))
Really, check out this report:
https://community.powerbi.com/t5/Data-Stories-Gallery/Contoso-by-SpartaBI/m-p/2449543
@yuvalpi you mean like this?:
Column 2 =
VAR _current_key = 'Table'[key]
VAR _result =
CALCULATE(
DISTINCTCOUNT( 'Table'[release] ),
REMOVEFILTERS('Table'),
'Table'[key] = _current_key,
'Table'[release] <> BLANK()
)
RETURN
_result
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |