Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
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
@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 :))
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
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?
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
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.
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
Proud to be a Super User!
@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! 😀
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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |