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)

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