Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:

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

  • tex628's avatar
    tex628
    6 years ago

    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

12 Replies

  • lkalawski's avatar
    lkalawski
    Icon for Resident Rockstar rankResident Rockstar

    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's avatar
      Anonymous
      Not applicable

      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)

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.

      • tex628's avatar
        tex628
        Icon for Community Champion rankCommunity Champion

        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