cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thangtruong Helper I
Helper I

NEED HELPS IN TABLE DAX FUNCTION

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)

 

Pic 6.PNG

 

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:

pic 7.PNG)

 

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
pic 8.PNG

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!

4 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support
Community Support

Re: NEED HELPS IN TABLE DAX FUNCTION

Hi @thangtruong ,

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.

NEED HELPS IN TABLE DAX FUNCTION - follow.png

NEED HELPS IN TABLE DAX FUNCTION - follow2.png

 

Best Regards,

Icey

 

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

Super User III
Super User III

Re: NEED HELPS IN TABLE DAX FUNCTION

@thangtruong 

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

View solution in original post

Super User III
Super User III

Re: NEED HELPS IN TABLE DAX FUNCTION

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

View solution in original post

Super User III
Super User III

Re: NEED HELPS IN TABLE DAX FUNCTION

@thangtruong 

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]

 

View solution in original post

14 REPLIES 14
Super User III
Super User III

Re: NEED HELPS IN TABLE DAX FUNCTION

Hi @thangtruong 

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

 

 

Highlighted
Community Support
Community Support

Re: NEED HELPS IN TABLE DAX FUNCTION

Hi @thangtruong ,

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. 

NEED HELPS IN TABLE DAX FUNCTION.PNG

 

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.

Super User III
Super User III

Re: NEED HELPS IN TABLE DAX FUNCTION

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

thangtruong Helper I
Helper I

Re: NEED HELPS IN TABLE DAX FUNCTION

@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

pic 9.PNG

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

 

thangtruong Helper I
Helper I

Re: NEED HELPS IN TABLE DAX FUNCTION

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
pic 10.PNG
 
 
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
pic 11.PNG
 
 
 
It's all in the google drive link in below
 
Please kindly take a look.
Really appriciate for your supports!!
Thanks in advance

 

 

Community Support
Community Support

Re: NEED HELPS IN TABLE DAX FUNCTION

Hi @thangtruong ,

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.

NEED HELPS IN TABLE DAX FUNCTION - follow.png

NEED HELPS IN TABLE DAX FUNCTION - follow2.png

 

Best Regards,

Icey

 

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

Super User III
Super User III

Re: NEED HELPS IN TABLE DAX FUNCTION

@thangtruong 

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

View solution in original post

Super User III
Super User III

Re: NEED HELPS IN TABLE DAX FUNCTION

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

View solution in original post

thangtruong Helper I
Helper I

Re: NEED HELPS IN TABLE DAX FUNCTION

@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 😞

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors