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.
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 :
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.
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".
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |