Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am working with data based on customers viewing a web page. I am trying to come up with a visual based on how many customers have viewed a web page based on groupings - I followed the article below to achieve this:
https://www.burningsuit.co.uk/blog/2018/06/dax-how-to-group-measures-into-numeric-ranges/
The ouput is as follows:
The problem is I know there are a lot of customers that haven't viewed the web page, but the total for the range "0" is empty, so somewhere along the line these customers are being ommitted.
I have made two attempts at this and both attempts are showign the exact same results.
Firstly the data,
I have a table called Customers which stores the customer's user name (plus other details)
The Customers table is on the One side of a one-to-many relationship with a Page Views table which contains a row for each "view" including a time stamp and the customer's user name.
I tried at this point to follow the article above, but everything was showing in the 1-5 bin - which (I believe) is because every view was just 1 view - it wasn't summarized by customer at this point. So I created a summary table (I tried twice) to overcome this problem, however both of these tables appear to be filtering out the customers with no page views. Here are my two calcualted tables:
Table = SUMMARIZE('Page Views','Page Views'[User Name],"Views",IF(COUNT('Page Views'[Time Stamp])>0,COUNT('Page Views'[Time Stamp]),0),"Count",1)
Table2 = SUMMARIZECOLUMNS('Page Views'[User Name],"Views",IGNORE(COUNT('Page Views'[Time Stamp])))
Please note in "Table" I tried to having the false value of the IF statement result in 999 instead of 0 but this had no effect.
So at this point it was destined to fail as the customers were already being filtered out, but I proceeded to create 2 measures (one for each table) in my bin table in the hope I could fix it later, here are those measures:
No of Totals by User USING TABLE =
IF(
HASONEVALUE(BinSelect[minValueRange]),
COUNTROWS(
FILTER('Table',
'Table'[Views]>=VALUES(BinSelect[minvalueRange])&&
'Table'[Views]<VALUES(BinSelect[MaxValueRange])
)
),
COUNTROWS('Table')
)
No of Totals by User USING TABLE 2 =
IF(
HASONEVALUE(BinSelect[minValueRange]),
COUNTROWS(
FILTER('Table2',
'Table2'[Views]>=VALUES(BinSelect[minvalueRange])&&
'Table2'[Views]<VALUES(BinSelect[MaxValueRange])
)
),
COUNTROWS('Table2')
My question is, how can I include the customers that haven't viewed a page - I know there are lots, and I believe I need to achieve this by modifying my calcualted table(s).
)
Solved! Go to Solution.
Create a new table
new table = UNION ( SUMMARIZE ( 'Page Views', 'Page Views'[user name], "count", VAR count_num = CALCULATE ( COUNT ( 'Page Views'[time stamp] ), ALLEXCEPT ( Customers, Customers[user name] ) ) RETURN IF ( count_num = 0, 0, count_num ) ), ADDCOLUMNS ( EXCEPT ( VALUES ( Customers[user name] ), VALUES ( 'Page Views'[user name] ) ), "count", 0 ) )
Add calculated columns in this new table
range = SWITCH ( TRUE (), [count] = 0, "0", [count] <= 5 && [count] >= 1, "1~5", [count] <= 10 && [count] >= 6, "6~10" ) min = VAR s = IF ( [range] <> "0", VALUE ( FIND ( "~", [range], 1, 0 ) ) - 1 ) RETURN IF ( [range] = "0", "0", LEFT ( [range], s ) ) max = VAR e = IF ( [range] <> "0", VALUE ( LEN ( [range] ) ) - VALUE ( FIND ( "~", [range], 1, 0 ) ) ) RETURN IF ( [range] = "0", "0", RIGHT ( [range], e ) ) total.no = CALCULATE ( DISTINCTCOUNT ( 'new table'[user name] ), ALLEXCEPT ( 'new table', 'new table'[range] ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Create a new table
new table = UNION ( SUMMARIZE ( 'Page Views', 'Page Views'[user name], "count", VAR count_num = CALCULATE ( COUNT ( 'Page Views'[time stamp] ), ALLEXCEPT ( Customers, Customers[user name] ) ) RETURN IF ( count_num = 0, 0, count_num ) ), ADDCOLUMNS ( EXCEPT ( VALUES ( Customers[user name] ), VALUES ( 'Page Views'[user name] ) ), "count", 0 ) )
Add calculated columns in this new table
range = SWITCH ( TRUE (), [count] = 0, "0", [count] <= 5 && [count] >= 1, "1~5", [count] <= 10 && [count] >= 6, "6~10" ) min = VAR s = IF ( [range] <> "0", VALUE ( FIND ( "~", [range], 1, 0 ) ) - 1 ) RETURN IF ( [range] = "0", "0", LEFT ( [range], s ) ) max = VAR e = IF ( [range] <> "0", VALUE ( LEN ( [range] ) ) - VALUE ( FIND ( "~", [range], 1, 0 ) ) ) RETURN IF ( [range] = "0", "0", RIGHT ( [range], e ) ) total.no = CALCULATE ( DISTINCTCOUNT ( 'new table'[user name] ), ALLEXCEPT ( 'new table', 'new table'[range] ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Firstly, let me confirm if i understand correctly.
I have uses a,b,c,d
user a view 3 times
user b view 8 times
user c view 10 times
user d view 0 times
so the table should be
range name no of total users
0 1
1-5 1
6-10 2
Right?
Best Regards
Maggie
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |