count in line chart with non-related tables

[newtopbitoo]

Newbie here.

I have a table with employeenumber and 3 weeknumbers  per record  (I have multiple periods over several years)
I have a weeknumbertable with weeknumber from 1 to 150.
In a line chart I want the weeknumber of the weeknumbertable on the x-axis and on the Y-axis I want to count the distinct employeenumber where weeknumber1 <= weeknumbertable[weeknumber] and weeknumber2 > weeknumbertable[weeknumber]

Because I have three different weeknumbers it is not possible to relate them to weeknumbertable (I think)

Thank you for your help.

[v-fenling-msft · Accepted solution]

Thanks for Kedar_Pande's concern about this issue.

Hi, @newtopbitoo 

I am glad to help you.

Since you didn't give the full dataset for conducting the test, I created a simple dataset for testing myself:

First a Measure will be created for the Kedar_Pande  suggested DAX:

DistinctEmployeeCount = 
VAR CurrentWeek = SELECTEDVALUE(WeekNumberTable[WeekNumber])
RETURN
CALCULATE(
DISTINCTCOUNT(EmployeeTable[EmployeeNumber]),
FILTER(
EmployeeTable,
EmployeeTable[WeekNumber1] <= CurrentWeek &&
EmployeeTable[WeekNumber2] > CurrentWeek
)
)

Then you can add a new Table visual to display EmployeeNumber as you said:

you need to drag the DistinctEmployeeCount to the Filter panel of the Table visual and set DistinctEmployeeCount to 1.

If you want to display it in Line chart visual, you can also create another Measure AllEmployeeNumber to display the conforming EmployeeNumber, and then drag the AllEmployeeNumber to the Tooltips in Line chart visual:

AllEmployeeNumber = 
VAR EmployeeList =
    CALCULATETABLE(
        VALUES(EmployeeTable[EmployeeNumber]),
        FILTER(
            EmployeeTable,
            EmployeeTable[WeekNumber1] <= SELECTEDVALUE(WeekNumberTable[WeekNumber]) &&
            EmployeeTable[WeekNumber2] > SELECTEDVALUE(WeekNumberTable[WeekNumber])
        )
    )
RETURN
CONCATENATEX(EmployeeList, EmployeeTable[EmployeeNumber], ", ")

I have attached the pbix file for this example below, hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newtopbitoo]

This does not solve the issue I am facing. When I select a weeknumber onnly one point is displayed in the line chart.

My goal is to display all weeknumbers and in another table or matrix the (more specified) values for one week

[Kedar_Pande]

@newtopbitoo 

Ensure you have two tables:
EmployeeTable (with EmployeeNumber, WeekNumber1, WeekNumber2, WeekNumber3)
WeekNumberTable (with WeekNumber from 1 to 150)

Create a New Measure

DistinctEmployeeCount = 
VAR CurrentWeek = SELECTEDVALUE(WeekNumberTable[WeekNumber])
RETURN
CALCULATE(
DISTINCTCOUNT(EmployeeTable[EmployeeNumber]),
FILTER(
EmployeeTable,
EmployeeTable[WeekNumber1] <= CurrentWeek &&
EmployeeTable[WeekNumber2] > CurrentWeek
)
)

Drag WeekNumber from WeekNumberTable to the X-axis of the line chart.
Drag the DistinctEmployeeCount measure you created to the Y-axis of the line chart.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[newtopbitoo]

Thank you for your quick reply.
This is indeed the solution I found myself too.
Problem is, that I use weeknumber as a selection for displaying values in another visual.  So when I select one week only one point is displayed in the line chart.
Should I add another weeknumber table or is there antoher way to make all weeknumbers displayed allthought the is a weeknumber selected ?