Forum Discussion

Rince91's avatar
Rince91
Helper I
2 years ago
Solved

Count matching column values based on visual selection

Hi All,

 

I have a bar chart showing the monthly payroll, Im trying to create another visual that will provide the number of people that have started in the month i have selected on that bar chart. Below is some example data.

 

For example if i select January Payroll date on the bar chart visual, I would like it to return 4.

 

Start DatePayroll DatePay
01 January 202328 January 2023£100.00
01 January 202328 February 2023£150.00
01 January 202328 March 2023£75.00
01 February 202328 February 2023£50.00
01 February 202328 March 2023£100.00
01 March 202328 April 2023£200.00
01 March 202328 March 2023£300.00
01 January 202328 February 2023£400.00
01 February 202328 March 2023£500.00
01 February 202328 March 2023£600.00

 

I have tried this measure but can't get it to work.

 

Joiners =

VAR selectedDate = SELECTEDVALUE('Table'[Payroll Date].[Month])

RETURN
CALCULATE(
    COUNT('Table'[Start Date]),FILTER('Table','Table'[Start Date].[Month] = selecteddate))
Need Help
  • OguzMavice's avatar
    OguzMavice
    2 years ago

    Hi Rince91 ,

     

    I think so that your master date table has relationship with Payroll Date of your Payroll Table. In this type situation, you can use "Userrelationship"  function in order to show  result based on your second date column. 

     

    First of all, you should create relationship between secondary date column and master date table.

    Then use this measure ;

     

    # People = CALCULATE(COUNT('Table'[Start Date]),USERELATIONSHIP('Date'[Date],'Table'[Start Date]))
     
    the result  ;
     

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • OguzMavice's avatar
    OguzMavice
    Resolver II
    2 years ago

    Hi Rince91 ,

     

    I think so that your master date table has relationship with Payroll Date of your Payroll Table. In this type situation, you can use "Userrelationship"  function in order to show  result based on your second date column. 

     

    First of all, you should create relationship between secondary date column and master date table.

    Then use this measure ;

     

    # People = CALCULATE(COUNT('Table'[Start Date]),USERELATIONSHIP('Date'[Date],'Table'[Start Date]))
     
    the result  ;
     

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.