Need help in Time intelligent DAX

[Anonymous]

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:

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:

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:

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

[amitchandak]

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

[Anonymous]

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

[tex628]

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

[Anonymous]

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

[tex628]

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 

[Anonymous]

Hi @tex628 

Yes, you're totally correct.

The Fact_Login Event [Column] will define the month between the Fact_Login Event [Login_Date] and the Dim_System_[CreatedDate]

It means the month between the date that user in that system had login and the day that system had been created.

I just care about the 1st month from the Created System Date, in another word, I just care about the Fact_Login Event [Column] = 1

[v-kelly-msft]

Hi @Anonymous ，

Is your issue solved now?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[tex628 · Accepted solution]

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

[Anonymous]

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.

[FarhanAhmed]

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

[lkalawski]

@Anonymous

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

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

[Anonymous]

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

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