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 (cal...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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
    8 years ago

    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
      8 years ago

      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

      • Quietlake's avatar
        Quietlake
        Regular Visitor
        8 years ago

        Hi Dale ( v-jiascu-msft ),

         

        Thanks for the quick reply.

        The daily line chart is definitely what I want in my original post and the measure_2 you show me by the picture is the right Month count.

        Sorry for my poor description about the second question.

        My second question is that is it possible to also draw two line charts like following graph ?

        Or it is impossible based on my data source ( Right now I do not have the ability to figure out possible or not)

         

        Best regards,

        Quietlake

  • Moliems's avatar
    Moliems
    Frequent Visitor
    4 years ago

    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