How to fix my DAX measures for distinct users but still be able to filter the date

[KejGdr]

Hi Community, 

I was informed that these are DAX measures. 

I have created a table with these, btw all are sources from a sharepoint online list

NGUsersItems = UNION(
    SELECTCOLUMNS('NG - Create Request List', "Name", 'NG - Create Request List'[Created By.title]), 
    SELECTCOLUMNS('NG - Get Templates Request List', "Name", 'NG - Get Templates Request List'[Created By.title] ),
    SELECTCOLUMNS('NG - Self Check Request List', "Name", 'NG - Self Check Request List'[Created By.title])
)

and I added a measure

TotalNGUsers = DISTINCTCOUNT(NGUsersItems[Name])

my problem with these are whenever I add a line chart, I cannot filter it by the date table[date]. I am fairly new to power bi and I do not understand why because there is a [Created] column in this data. Any form of help or insights how I can fix this will highly be appreciated with a like and be marked as solution if it solves my problem. Thank you! 

This is how it looks currently. What I want it to look like is show the number of total users per year.

kind of like this (this is a different measure which does not tackle the number of users but the entries)

[Ahmed-Elfeel]

Hi @KejGdr,

I hope you are doing well today 😄❤️

Looking at your issue The Problem is that your NGUsersItems table is disconnected from your date table, Since it is a calculated table created with UNION() , it does not maintain relationships with your original data tables

Here are a few Approaches to fix your DAX measures:

First Approach :

Replace your calculated table with measures that can interact with date filters (Instead of Calculated Table😞

-- Individual measures for each table
NG_Create_Users = 
CALCULATE(
    DISTINCTCOUNT('NG - Create Request List'[Created By.title]),
    USERELATIONSHIP('Date'[Date], 'NG - Create Request List'[Created])
)

NG_GetTemplates_Users = 
CALCULATE(
    DISTINCTCOUNT('NG - Get Templates Request List'[Created By.title]),
    USERELATIONSHIP('Date'[Date], 'NG - Get Templates Request List'[Created])
)

NG_SelfCheck_Users = 
CALCULATE(
    DISTINCTCOUNT('NG - Self Check Request List'[Created By.title]),
    USERELATIONSHIP('Date'[Date], 'NG - Self Check Request List'[Created])
)

-- Combined measure
TotalNGUsers = 
VAR CreateUsers = [NG_Create_Users]
VAR GetTemplatesUsers = [NG_GetTemplates_Users]
VAR SelfCheckUsers = [NG_SelfCheck_Users]
RETURN
    CreateUsers + GetTemplatesUsers + SelfCheckUsers

Second Approach:

• Create a calculated table that maintains the date relationships:

NGUsersWithDates = 
UNION(
    SELECTCOLUMNS(
        'NG - Create Request List', 
        "Name", 'NG - Create Request List'[Created By.title],
        "Date", 'NG - Create Request List'[Created]
    ),
    SELECTCOLUMNS(
        'NG - Get Templates Request List', 
        "Name", 'NG - Get Templates Request List'[Created By.title],
        "Date", 'NG - Get Templates Request List'[Created]
    ),
    SELECTCOLUMNS(
        'NG - Self Check Request List', 
        "Name", 'NG - Self Check Request List'[Created By.title],
        "Date", 'NG - Self Check Request List'[Created]
    )
)

Third Approach: (most efficient approach)

• Use SUMMARIZE and CALCULATE :

TotalNGUsers = 
VAR CombinedTable =
    UNION(
        SUMMARIZE(
            'NG - Create Request List',
            'NG - Create Request List'[Created By.title],
            'NG - Create Request List'[Created]
        ),
        SUMMARIZE(
            'NG - Get Templates Request List',
            'NG - Get Templates Request List'[Created By.title],
            'NG - Get Templates Request List'[Created]
        ),
        SUMMARIZE(
            'NG - Self Check Request List',
            'NG - Self Check Request List'[Created By.title],
            'NG - Self Check Request List'[Created]
        )
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT(CombinedTable[Created By.title]),
        USERELATIONSHIP(CombinedTable[Created], 'Date'[Date])
    )

Bonus Tips:

• Make sure your date table:

  • Has a proper date hierarchy

  • Is marked as a date table

  • Has relationships with your fact tables

• For cumulative totals (if needed):

CumulativeNGUsers = 
CALCULATE(
    [TotalNGUsers],
    FILTER(
        ALLSELECTED('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

The idea here is that you need to preserve the date column in your combined table and establish proper relationships with your date table for the time based filtering to work correctly

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[KejGdr]

@bhanu_gautam  

Thank you for your response it worked but different to what I am aiming for in the visual. How can I make it so that the visual show the total of users per date? 

for example for Nov 2025 it shows 6 distinct users were added but it also shows the total number of distinct users for this said month

EDIT: also apologies for this newbie questions, but the visual kinda zooms out quite far a bit is there anything I can do to not show dates that doesnt have data so that it can only show the relevant dates which has users and entries?

[ValtteriN]

Hi,

Like mentioned by bhanu make sure you have created a relationship between your tables.

Regarding your EDIT question. You can change the axis to categorical to achieve this. E.g.

Data:

I have relationship from 'Table (2)'[date] to 'calendar[date]'. With "continuous" setting the visual looks like this:

Now with "categorical" only dates with values are shown.

Note that this will not appear if you have date hierachy in the axis. 

[bhanu_gautam]

@KejGdr 

Modify your DAX to include the [Created] column:

DAX
NGUsersItems =
UNION(
SELECTCOLUMNS('NG - Create Request List', "Name", 'NG - Create Request List'[Created By.title], "Created", 'NG - Create Request List'[Created]),
SELECTCOLUMNS('NG - Get Templates Request List', "Name", 'NG - Get Templates Request List'[Created By.title], "Created", 'NG - Get Templates Request List'[Created]),
SELECTCOLUMNS('NG - Self Check Request List', "Name", 'NG - Self Check Request List'[Created By.title], "Created", 'NG - Self Check Request List'[Created])
)

Create a relationship between NGUsersItems[Created] and your Date table[Date]:

Go to the Model view.
Drag NGUsersItems[Created] to Date[Date] to create a relationship (make sure the data types match).
Update your measure if needed

TotalNGUsers = DISTINCTCOUNT(NGUsersItems[Name])