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.
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
User | Count |
---|---|
16 | |
14 | |
13 | |
12 | |
11 |
User | Count |
---|---|
19 | |
15 | |
15 | |
11 | |
10 |