Forum Discussion

Quietlake's avatar
Quietlake
Regular Visitor
8 years ago
Solved

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] )
        )
    )
    

    Count_distinct_between_two_date_with_date_slicer_for_line_chart

     

    Best Regards,

    Dale

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft 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] )
        )
    )
    

    Count_distinct_between_two_date_with_date_slicer_for_line_chart

     

    Best Regards,

    Dale

    • Quietlake's avatar
      Quietlake
      Regular 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's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft 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] )
            )
        )
        

        Count_distinct_between_two_date_with_date_slicer_for_line_chart2

         

        Best Regards,

        Dale

    • Moliems's avatar
      Moliems
      Frequent 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