Forum Discussion
Count distinct between two date with date slicer for line chart
Hi, I'm new to Power BI & DAX.
I had learned a lot from posts in this community.
I encounter a problem and can not find a suitable way when I search the community.
Sample Source Data Table (called "Records"):
Date_of_Application user_id membership_period_start membership_period_end
2018/6/8 a 2018/6/11 2018/6/17
2018/6/8 b 2018/6/11 2018/6/13
2018/6/11 c 2018/6/15 2018/6/19
The result I try to generate from the "Records" table:
Every Day Distinct Member Count Table:
Date Count explain (Describe my problem, no need to be in the table)
2018/6/11 2 (a,b)
2018/6/12 2 (a,b)
2018/6/13 2 (a,b)
2018/6/14 1 (a)
2018/6/15 2 (a,c)
2018/6/16 2 (a,c)
2018/6/17 2 (a,c)
2018/6/18 1 (c)
2018/6/19 1 (c)
Every Week Distinct Member Count Table:
Week_Date_Start Week_Date_End Count explain
2018/6/11 2018/6/17 3 (a,b,c)
2018/6/18 2018/6/24 1 (c)
Every Month Distinct Member Count Table:
Month_Date_Start Month_Date_End Count explain
2018/6/1 2018/6/30 3 (a,b,c)
The method I tried:
First, I come out this DAX:
2018_6_11_measure =
calculate(
DISTINCTCOUNT(RECORDS[user_id]),
FILTER(RECORDS, RECORDS[membership_period_start] <= DATE(2018,6,11)),
FILTER(RECORDS, RECORDS[membership_period_end] >= DATE(2018,6,11)))
Then, I create a Date Table
Date
2018/1/1
...
2018/6/1
...
2018/12/31
But I can not find out any way to relate Date table and Records table
Then, I try Power BI Line chart:
Axis : I want to put Date[Date]
Values: Records[user_id] , set to Count(Distinct)
And start play with Visual level filters or Slicer, but failed
Need Help
Hi Quietlake,
Please check out the demo in the attachment.
1. Create a date table.
Calendar = ADDCOLUMNS ( CALENDARAUTO (), "MonthNum", MONTH ( [Date] ), "WeekNum", WEEKNUM ( [Date], 2 ) )2. DO NOT establish relationships.
3. Create a measure.
Measure = CALCULATE ( DISTINCTCOUNT ( count_distinct[user_id] ), FILTER ( 'count_distinct', 'count_distinct'[membership_period_start] <= MIN ( 'Calendar'[Date] ) && 'count_distinct'[membership_period_end] >= MIN ( 'Calendar'[Date] ) ) )Best Regards,
Dale
5 Replies
- v-jiascu-msft
Microsoft Employee
Hi Quietlake,
Please check out the demo in the attachment.
1. Create a date table.
Calendar = ADDCOLUMNS ( CALENDARAUTO (), "MonthNum", MONTH ( [Date] ), "WeekNum", WEEKNUM ( [Date], 2 ) )2. DO NOT establish relationships.
3. Create a measure.
Measure = CALCULATE ( DISTINCTCOUNT ( count_distinct[user_id] ), FILTER ( 'count_distinct', 'count_distinct'[membership_period_start] <= MIN ( 'Calendar'[Date] ) && 'count_distinct'[membership_period_end] >= MIN ( 'Calendar'[Date] ) ) )Best Regards,
Dale
- QuietlakeRegular Visitor
Hi Dale( v-jiascu-msft )
Thank you so much. It Looks like I need to learn more difference between Measure and Column.
I try to play around the .pbix you provide and encounter another problem.
In your reply, you create 2 column [MonthNum] and [WeekNum].
I try to drag these to Axis or Legend or Value, but I could not generate the result I asked in my problem
Every Week Distinct Member Count Table:
Week_Date_Start Week_Date_End Count explain
2018/6/11 2018/6/17 3 (a,b,c)
2018/6/18 2018/6/24 1 (c)Every Month Distinct Member Count Table:
Month_Date_Start Month_Date_End Count explain
2018/6/1 2018/6/30 3 (a,b,c)I also try to rewrite the dax, but have no idea.
Measure_week = CALCULATE ( DISTINCTCOUNT ( count_distinct[user_id] ), FILTER ( 'count_distinct', 'count_distinct'[membership_period_start] <= MIN ( No idea ) && 'count_distinct'[membership_period_end] >= MIN ( No idea ) ) )Best Regards,
Quietlake
- v-jiascu-msft
Microsoft Employee
Hi Quietlake,
You said you want a line chart in your first post. Regarding to the question here, the solution could be like below. Because there isn't a start date of 2018/6/1. Please refer to the snapshot.
Measure 2 = CALCULATE ( DISTINCTCOUNT ( count_distinct[user_id] ), FILTER ( 'count_distinct', 'count_distinct'[membership_period_start] >= MIN ( 'Calendar'[Date] ) && 'count_distinct'[membership_period_end] <= MAX ( 'Calendar'[Date] ) ) )Best Regards,
Dale
- MoliemsFrequent Visitor
Hi there, this has been really helpful for a similar problem I am working with.
In addition to above, I would like to include members in the distinct count who have a blank membership end date (they are still members). Can you advise how the Dax above could to modified?
Thank you