cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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!

14 REPLIES 14
Highlighted

@thangtruong 

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

 

   

 

Highlighted
Helper II
Helper II

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

pic 12.PNG

 

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 😞 

Highlighted

@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

Highlighted

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

Now my reports are working.

Really appreciate, Mr/Mrs @AlB 

 

Highlighted

@thangtruong 

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

Cheers

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors