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

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

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

@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

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 

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors