Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
_SpiderPig_
New Member

Deduplicate measure in decomposition tree

Hello!


Please help me figure it out or point me in the right direction, because my skills in DAX are not enough to solve this problem


Problem:
I have a table with users with filters and funnel levels, as :

_SpiderPig__3-1681478145567.png


i have to find the number of unique users in each level using Decomposition tree.
The measure : cnt:= DISTINCTCOUNT('Table'[User])

I get a tree where a user can be counted multiple times at the same level.

_SpiderPig__7-1681478289361.png


My task is to make it so that after filtering, use such a data set, where the user is taken into account once at each level.

Expected tree work by stages:
1) Еhe tree was not filtered, so according to the data we see that all users had an ACTION at each stage of the tree
2) The user selects number "3" in the filter "Product". 

_SpiderPig__5-1681478218640.png
But the desired result is to have each user учусгеу an ACTION on each level.
The same in SQL:
1) we have the original table
2) user clicks various filters in the report, for example product is 3. So we have stage table in format:
select user, max(level_1) as lvl1, max(level_2) as lvl2, max(level_3) as lvl3
from original_table
where product = 3
group by user
3) Columns with maximum values are fields in the decomposition tree showing whether the user performed an ACTION in the context of such filtering, and if he did not, he falls into "No ACTION"
Thus the user will be counted once at each stage of the tree.


I hope I made the information clear enough.

Any help would be invaluable and many thanks!!!


P.S.
I tried to make through Calculate table:
SUMMARIZE(
   Table,Table[user], "lvl1", MAX(Table[level_1]), "lvl2", MAX(Table[level_2]), "lvl3", MAX(Table[level_3])
)
but the tables in Dax are not dynamic



0 REPLIES 0

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.