Forum Discussion
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
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
Resident 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! 😀- AnonymousNot applicable
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)
- FarhanAhmed
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
- amitchandak
Super User
Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
- AnonymousNot 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.
- AnonymousNot 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
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