NEED HELPS IN TABLE DAX FUNCTION

[Anonymous]

Hi all,

I'm newbie in here. I got a problem that really need your helps 😞

I'm building customer reports and I have a raw table like this:

Col1: User's name

Col2: User's email

Col3: User's System ID

Col4: Created Date

(I highlighted the important columns in the picture below)

Now I want to create a new table, based on above raw table.

The table I want will look like:

Col1: Date

Col2: System_ID

Col3: Accumulated Created Users

(In this case, I want this result will be:

)

I tried this DAX: 

= SUMMARIZE('base Users','base Users'[system_id],'base Users'[Created Date 2],"Accumulated Created Table",CALCULATE(COUNT('base Users'[email]),FILTER(ALL('Date'[Date]),ISONORAFTER('Date'[Date],MAX('Date'[Date]),DESC))))

But here is the result

As you can see, the result is failed because of 2 reasons:

1. I want the date is sort DESC

2. The Accumulated Column is not correct.

Please kindly take a look and give me some advisers.

Thansks in advance!

[Icey · Accepted solution]

Hi @Anonymous ,

You can change your expression like so:

SUM TABLE 1 =
SUMMARIZE (
    USER,
    USER[system_id],
    USER[Created Date],
    "Accumulated Users", CALCULATE (
        COUNT ( USER[email] ),
        FILTER ( ALL ( USER ), USER[Created Date] <= EARLIER ( USER[Created Date] ) )
    )
)

Then, choose 'sort ascending' manually.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB · Accepted solution]

@Anonymous 

Try this:

SUM TABLE 2C =
ADDCOLUMNS (
    SUMMARIZE ( USER; USER[system_id]; USER[Created Date] );
    "Accumulated Users"; CALCULATE (
        COUNT ( USER[email] );
        FILTER (
            ALL ( USER[Created Date] );
            ISONORAFTER ( USER[Created Date]; EARLIER ( USER[Created Date] ); DESC )
        )
    )
)

or you could implement the EARLIER with VAR instead

[AlB · Accepted solution]

Although I would prefer to do it like this, without the ISONORAFTER:

SUM TABLE 2B =
ADDCOLUMNS (
    SUMMARIZE ( USER; USER[system_id]; USER[Created Date] );
    "Accumulated Users"; CALCULATE (
        COUNT ( USER[email] );
        USER[Created Date] <= EARLIER ( USER[Created Date] )
    )
)

where you could also use VAR instead of EARLIER

[Anonymous]

@AlB thanks for your comment :))

Sorry for disturbing you again, but another problems come ups.

Thanks to your helps, I already got the result of Accumulated Created Users.

The final result I want is % Active in day = Divide ( Number of active user in day ; Accumulated Created Users)

To calculated Number of active users in day, I used this DAX:

Active users in day = DISTINCTCOUNT('LOGIN TABLE'[email])

To calculated % Active in day, I used this DAX:

% Active in day = 
DIVIDE([Active users in day], SUM('CREATED USERS'[Accumulated Users]))

But here is the result:

The correct resutl should be like that:

7/17/2019 16 10 10/16

7/18/2019 16 15 15/16

7/19/2019 16 14 14/16

7/20/2019 16 10 10/16

7/21/2019 16 2 2/16

7/22/2019 16 10 10/16

7/23/2019 17 13 13/17

....

I dont know what DAX should be used in this case 😞

I also attached the file and raw data in the link below. Please kindly take a look

https://drive.google.com/drive/folders/1yzpTgsXTbPLNS-hdIAjsdhq-f-wwdjdt?usp=sharing

Thank you so much 😞 

[AlB · Accepted solution]

@Anonymous 

Considering what you are trying to do now, I think the table we created earlier is not very useful. I'd suggest the following:

1. Create a one-to-many relationship between DATE and USER (Date --> Created Date)

2. Create a measure that will do the cumulative directly on the visual:

Measure_Accumulated_Users =
CALCULATE (
    COUNT ( 'USER'[email] );
    FILTER ( ALL ( 'DATE'[Date] ); 'DATE'[Date] <= MAX ( 'DATE'[Date] ) )
)

 This will give you a value in all rows. Then you can update your other two measures accordingly, using [Measure_Accumulated_Users]

[Anonymous]

@AlB I dont know what to say right now. It's all solved now.

Now my reports are working.

Really appreciate, Mr/Mrs @AlB 

[AlB]

@Anonymous 

Great. My pleasure. It's always nice to come across someone so grateful.

Cheers

[Icey]

Hi @Anonymous ,

I reproduced your question, but no error appeared. My result is as the screenshot below.

The 'base Users' table is part of your raw table. And the 'Date' table is created automatically use 'CALENDARAUTO'. The 'Sum Table' is created by your DAX expression. You can download my PBIX file . 

Maybe you can try it again. If it still doesn't work,  you can provide more details so that I can help you better. 

Best Regards,

Icey Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey 

Thanks to your response.

I already download your file. But It seem like some thing is not correct with that data :))

In my raw table, I just only have 18 users in total. But when I look at your file, I see it is 78 users in totals

I just attached my pwbi in google drive link in below. Please kindly take a look

https://drive.google.com/drive/folders/1QsDKn3dMR20hhkDID8GEo90j_Wcrv2aH?usp=sharing

Thank you so much :))

[Icey · Accepted solution]

Hi @Anonymous ,

You can change your expression like so:

SUM TABLE 1 =
SUMMARIZE (
    USER,
    USER[system_id],
    USER[Created Date],
    "Accumulated Users", CALCULATE (
        COUNT ( USER[email] ),
        FILTER ( ALL ( USER ), USER[Created Date] <= EARLIER ( USER[Created Date] ) )
    )
)

Then, choose 'sort ascending' manually.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @Anonymous 

What is 'Date'? A calendar table with a relationship to 'base Users',? Why are you using it in

 FILTER(ALL('Date'[Date]),ISONORAFTER('Date'[Date],MAX('Date'[Date]),DESC)) 

instead of  'base Users'[Created Date 2]?

With SUMMARIZE you will have row/filter context on the date and id in the current row so that is why you're getting the count for only that date. You need to cancel that, which is what I guess you're trying to do with

FILTER(ALL('Date'[Date]),ISONORAFTER('Date'[Date],MAX('Date'[Date]),DESC)) 

but that needs to be done not on the 'Date' table but on the  'base Users' table, which where you're doing the SUMMARIZE over. You might also need to use ASC instead of DESC. Something like this, although I don't have all the information:

Table =
SUMMARIZE (
    'base Users',
    'base Users'[system_id],
    'base Users'[Created Date 2],
    "Accumulated Created Table", CALCULATE (
        COUNT ( 'base Users'[email] ),
        FILTER (
            ALL ( 'base Users'[Created Date 2] ),
            ISONORAFTER (
                    'base Users'[Created Date 2], MAX ( 'base Users'[Created Date 2] ), DESC
            )
        )
    )
)

[AlB]

You might also want to consider using the construct 

NewTable =
ADDCOLUMNS (
    SUMMARIZE ( Table1; Table1[Col1]; Table1[Col2] );
    "NewCol"; CALCULATE ( COUNT ( Table1[Col] ) )
)

instead of adding the new column within the SUMMARIZE, which is not recommended any more. Check it out on this article

[Anonymous]

Hi @AlB 

It's really nice to hear from you

I tried both methods that you suggested. But It still didn't work.

I just attacthed the file in below, that:

SUM TABLE 1: (Using SUMMARIZE FUNCTION)

SUMMARIZE(USER, USER[system_id],USER[Created Date],"Accumulated Users",CALCULATE(COUNT(USER[email]),FILTER(ALL(USER[Created Date]),ISONORAFTER(USER[Created Date],MAX(USER[Created Date]),DESC))))

The result is

SUM TABLE 2: (Using ADDCOLUMN FUNCTION)

ADDCOLUMNS(SUMMARIZE(USER,USER[system_id],USER[Created Date]),
    "Accumulated Users",
    CALCULATE(COUNT(USER[email]),
        FILTER(ALL(USER[Created Date]),
            ISONORAFTER(USER[Created Date],
                MAX(USER[Created Date]),
                DESC)
        )
    )
)

The result is

It's all in the google drive link in below

https://drive.google.com/drive/folders/1yzpTgsXTbPLNS-hdIAjsdhq-f-wwdjdt?usp=sharing

Please kindly take a look.

Really appriciate for your supports!!

Thanks in advance

[AlB · Accepted solution]

@Anonymous 

Try this:

SUM TABLE 2C =
ADDCOLUMNS (
    SUMMARIZE ( USER; USER[system_id]; USER[Created Date] );
    "Accumulated Users"; CALCULATE (
        COUNT ( USER[email] );
        FILTER (
            ALL ( USER[Created Date] );
            ISONORAFTER ( USER[Created Date]; EARLIER ( USER[Created Date] ); DESC )
        )
    )
)

or you could implement the EARLIER with VAR instead

[AlB · Accepted solution]

Although I would prefer to do it like this, without the ISONORAFTER:

SUM TABLE 2B =
ADDCOLUMNS (
    SUMMARIZE ( USER; USER[system_id]; USER[Created Date] );
    "Accumulated Users"; CALCULATE (
        COUNT ( USER[email] );
        USER[Created Date] <= EARLIER ( USER[Created Date] )
    )
)

where you could also use VAR instead of EARLIER

[Anonymous]

@Icey  @AlB 

Really appriciate to you guys.

It works now, in both methods that you guys suggested.

Thank you so much :))

I should spend more time to learn about how DAX work 😞

[AlB]

@Anonymous 

No worries. You were actually quite close already, only a minor modification was missing. Just a matter of some more practice.

On a different, note please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix  (which is what you did at the end and got you the solution pretty quickly :-). Beware of confidential data.

Cheers