topic Re: Disctinct count in daterange with visual calculations in DAX Commands and Tips

Disctinct count in daterange with visual calculations

somogyitamas88 — Tue, 13 Jan 2026 13:52:46 GMT

Hi all!

I have an activity table with dates and user's operations. I need to calculate for a line chart (with timeline) a number for each date with unique user id-s on the previous 7 days.

I've created a DAX function over the datamodel with window function, but it's too slow. So I would like to replace it with a visual calculation formula where I put the date and userid fields to the line chart from the model.

What should be the formula then?

Re: Disctinct count in daterange with visual calculations

deborshi_nag — Tue, 13 Jan 2026 14:42:47 GMT

Hi somogyitamas88

Solution: For each date on the X‑axis, count the unique User IDs over the previous 7 calendar days (including that date).

- Put Date on the line chart X‑axis (continuous, daily).

- Drag UserId into the visual so that it’s available to the calculation without cluttering the chart.

- With the visual selected, choose Home ▸ New calculation to open the Visual Calculation editor.

Use the following visual calculation formula -

Active Users (7D, VC) =

COUNTROWS(

DISTINCT(

SELECTCOLUMNS(

WINDOW( -6, 0, ORDERBY([Date], ASC) ),

"U", [UserId]

)

Hope this helps - please appreciate by leaving a Kudos or accepting it as a Solution to help others!

Re: Disctinct count in daterange with visual calculations

somogyitamas88 — Tue, 13 Jan 2026 14:54:40 GMT

Hi, it only shows the distinct num of dates in the previous 7 days. (Mainly show 7, but when there are no activity on a day, its less).

Re: Disctinct count in daterange with visual calculations

deborshi_nag — Tue, 13 Jan 2026 15:00:32 GMT

Hi @somogyitamas88

The visual calculation is operating on one row per Date, so the WINDOW is returning 7 date rows, and the DISTINCT(...) is effectively counting dates—not users.

Try the following change:

X‑axis: put your Date (continuous, daily).
Y‑axis: any metric (you can even use a dummy measure like 1).
Details: add UserId (this is the key step).
- If the chart explodes into too many series, don’t worry—Power BI’s line chart won’t draw a separate line per detail value; Details just increases row-granularity for the calc.
- With the visual selected, Home ▸ New calculation and paste:
  Active Users (7D, VC) =
  COUNTROWS(
      DISTINCT(
          SELECTCOLUMNS(
              WINDOW( -6, 0, ORDERBY([Date], ASC) ),
              "U", [UserId]
          )
      )
  )

Re: Disctinct count in daterange with visual calculations

MasonMA — Tue, 13 Jan 2026 15:40:19 GMT

Hi,

You can use this below for visual calculation.

test =

CALCULATE(

SUM([UserID]),

WINDOW(

-6, 0,

ORDERBY([Activity Date]

)

Re: Disctinct count in daterange with visual calculations

cengizhanarslan — Tue, 13 Jan 2026 18:16:37 GMT

Instead of window function could you try the formula below:

Active Users (7D) = VAR EndDate = MAX ( 'DimDate'[Date] ) RETURN CALCULATE( DISTINCTCOUNT ( Activity[UserId] ), DATESINPERIOD ( 'DimDate'[Date], EndDate, -7, DAY ) )

Re: Disctinct count in daterange with visual calculations

FBergamaschi — Tue, 13 Jan 2026 18:41:24 GMT

Hi somogyitamas88,

I see the code was alreayd given to you so I will not go on that

My worry is that you realize than the visual calculation will give a different result than a measure on the first 6 days if you apply a filter to the dates in the report

Imagine you filter a single month, say November 2025. On 1st of nov 205 I assume you would like to see a number depending on the last 7 days of october. But that, with a visual calculation, is impossible

Just wanted you to be aware of this

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Disctinct count in daterange with visual calculations

Kedar_Pande — Wed, 14 Jan 2026 05:33:11 GMT

somogyitamas88

7D Unique Users = 
RANKX(
WINDOW(-6,0,ALLSELECTED(Date[Date]),ORDERBY(Date[Date])),
DISTINCTCOUNT(Activity[UserID]),
,
ASC
)

Date (X-axis), UserID (small multiples or legend), measure above (Y)

Re: Disctinct count in daterange with visual calculations

somogyitamas88 — Wed, 14 Jan 2026 08:28:24 GMT

Unfortunately it's too slow.

This is my current one:

NumOfUsers7day_timeline =

CALCULATE(

[NumOfUsers],

WINDOW(

-6, 0,

REL,

ORDERBY('Date'[Dates], ASC)

)

It's faster however with more 10-15 sec loading.

Re: Disctinct count in daterange with visual calculations

somogyitamas88 — Wed, 14 Jan 2026 08:28:52 GMT

Thank you.

Re: Disctinct count in daterange with visual calculations

somogyitamas88 — Wed, 14 Jan 2026 08:30:54 GMT

For me Details accepts only measure (at least implicit one). So I can drag User ID nowhere.

Re: Disctinct count in daterange with visual calculations

v-echaithra — Tue, 20 Jan 2026 11:10:43 GMT

Hi somogyitamas88 ,

Thank you FBergamaschi , Kedar_Pande for your inputs.

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you

Re: Disctinct count in daterange with visual calculations

v-echaithra — Tue, 27 Jan 2026 04:57:34 GMT

Hi somogyitamas88 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.