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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PetyrBaelish
Resolver III
Resolver III

Summarize function not including users with 0 values

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:

Enc_ForForum.PNG

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).
)

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @PetyrBaelish 

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
    )
)

12.png

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] )
)

11.png

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.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @PetyrBaelish 

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
    )
)

12.png

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] )
)

11.png

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.

v-juanli-msft
Community Support
Community Support

Hi @PetyrBaelish 

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

Hi @v-juanli-msft 

 

That's correct

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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