Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
tex628
Community Champion
Community Champion

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
amitchandak
Super User
Super User

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

 

 

Anonymous
Not applicable

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
Community Champion
Community 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


Connect on LinkedIn
Anonymous
Not applicable

@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
Community Champion
Community Champion

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
Anonymous
Not applicable

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

Hi @Anonymous ,

 

Is your issue solved now?

 


 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
tex628
Community Champion
Community Champion

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

FarhanAhmed
Community Champion
Community Champion

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!!

Proud to be a Super User!




lkalawski
Memorable Member
Memorable Member

@Anonymous

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! 😀

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.

4.PNG

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.