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

Need help in Time intelligent DAX

Hi experts, I'm newbie in this field. I stucked with an DAX formular for few weeks :((

 

Here is my context:

Calculated the Current Users day by day.

Current User means: How many cumulative CREATED users at that day.

Table: Fact_Login Event: contain LOGIN event for each user

Table: Dim_Total User: List of created users

 

Please kindly look at my model:

2.PNG

I used this DAX to calculated the current user each day

 

Current Usser = 
CALCULATE (
    COUNTX (
        FILTER (
            'Dim_Total Users',
            'Dim_Total Users'[Created Date] <= MAX ( 'Date'[Date] )
                && (
                    ( ISBLANK ( 'Dim_Total Users'[deactivate_date] ) = TRUE () )
                        || ( 'Dim_Total Users'[deactivate_date] >= MAX ( 'Date'[Date] ) )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[reactivated_date] <= 'Dim_Total Users'[deactivate_date]
                                && 'Dim_Total Users'[status] <> -1
                        )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[status] <> -1
                        )
                )
                && 'Dim_Total Users'[metatype] <> "guest"
        ),
        'Dim_Total Users'[id]
    ),
    CROSSFILTER ( 'Dim_Total Users'[Created Date], 'Date'[Date], NONE )
)

 

This is the result:

1.PNG

As you can see, with the above DAX, I will get all the current user for each day, from the 1st day of this specific company (7/3/2020) till the end of date in Date table.

 

But now, I just want to see only result in 1 specific month, for example, in July 2020.

Step 1: I added a caculated column in Fact_Login Event, to detect event that happend in July - 2020, I will marked "1" to this event.

Step 2: I re-write the DAX like below

 

Current Usser m+1 = 
CALCULATE(CALCULATE (
    COUNTX (
        FILTER (
            'Dim_Total Users',
            'Dim_Total Users'[Created Date] <= MAX ( 'Date'[Date] )
                && (
                    ( ISBLANK ( 'Dim_Total Users'[deactivate_date] ) = TRUE () )
                        || ( 'Dim_Total Users'[deactivate_date] >= MAX ( 'Date'[Date] ) )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[reactivated_date] <= 'Dim_Total Users'[deactivate_date]
                                && 'Dim_Total Users'[status] <> -1
                        )
                        || (
                            'Dim_Total Users'[reactivated_date] <= MAX ( 'Date'[Date] )
                                && ( ISBLANK ( 'Dim_Total Users'[reactivated_date] ) = FALSE () )
                                && 'Dim_Total Users'[status] <> -1
                        )
                )
                && 'Dim_Total Users'[metatype] <> "guest"
        ),
        'Dim_Total Users'[id]
    ),
   CROSSFILTER ( 'Dim_Total Users'[Created Date], 'Date'[Date], NONE )),FILTER(Fact_Login Event,Fact_Login Event[Column] = 1))

 

(Just adding "FILTER(Fact_Login Event,Fact_Login Event[Column] = 1)" at the end)

Here is the result:

3.PNG

 

It's seem like good. But wait... I lost many dates in July: 7/4/2020, 7/5/2020 ..... That's not what I want. The correct result will look like:

- Whole date in July-2020 (start from 7/3/2020)

- Just only date in July-2020 (no data from Aug, Sep...)

 

The reason why it lost date :7/4/2020, 7/5/2020 ... because of there is no data for those date in table Fact_Login Event.

 

Please help me fix the 2nd DAX to show whole day in July 2020.

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

It's quite hard to tell if things are working with more than one customer 🙂 But try the attached file, i've added a mapping table and changed the filtering around a little. 

/ J


Connect on LinkedIn

View solution in original post

12 REPLIES 12
Highlighted
Solution Sage
Solution Sage

@thangtruong

1. Use a date in the Date table.
2. Select Show items with no data.

lkalawski_0-1598848327838.png

 



_______________
If I helped, please accept the solution and give kudos! 😀

Highlighted
Memorable Member
Memorable Member

Right click on the field on visual and click "show items with no data"

 

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Highlighted
Super User IV
Super User IV

@thangtruong ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@lkalawski 

@FarhanAhmed 

really thanks for your quick response.

But it's seem not what I want.

When I tried to follow your solution, here is the result.

4.PNG

The correct data what should be fill in 7/4/2020, 7/5/2020 ... is 13 (not blank)

Highlighted

Yes, Thanks for your response. I'm working on the sample file and it would be really nice to have your attention in my question.

I will let you have my sample file today.

Highlighted

Hi Mr @amitchandak , please take a look at sample file in this google drive link below:

https://drive.google.com/drive/folders/1Hr-lXUAD1k3BHyHPfyRvJskKQFqnPDfx?usp=sharing

 

Hope to hear from you soon

Highlighted

You need to apply that last filter to the Date table instead of the Fact_login table. Take a look at the attached file:

Currently your facts table is filtering the total_user table through the date table and I can't really tell you why. If anyone has an explaination as to why I'm all ears! 🙂

/ J


Connect on LinkedIn
Highlighted

@tex628  thanks for your response :)) But the DAX you suggest maybe not work for me.

The sample file is the example of 1 customer - system id : 3333, so that the month need to collect here is July - 2020

But I have another thousands customers, and they have diffirentes month to collect, maybe Aug/2020, maybe Apr.2020 ...

So, in the DAX you have, you had fixed the date is "202007" and it will not work in another case :))

Highlighted

What exactly is Fact_Login Event[Column] defining? 

Is it counting difference of months from the current row login date to the creation date of the system?

/ J 


Connect on LinkedIn

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors