Unable to filter by gender in my chart

[JohnBSL]

I'm fairly new to PBI so hopefully it is an easy fix. Essentially, I have two tables, one of which is a calculated table based on the first one and I'm trying to use one of the fields in the first table to filter the data in the second table.

So my [Data] table is set up like so....

ClientID	Last Date of Month	Gender
001	30/6/2021	M
002	31/7/2021	F
003	30/9/2020	F

And my calculated table [Start Date Table] is set up like so (I've included the formulas I've used for reference)....

Last Date of Month	Total enrolments in month
= SUMMARIZE(Data,Data[Last Date of Month])	= CALCULATE(COUNT((Data[ClientID])))
30/6/2021	8
31/7/2021	10

In terms of table relationships, I have created a *:1 bi-directional relationship between the Last Date of Month columns in each table (with the Data table being the many side of the relationship).

On the visualisation side of things, I'm creating a basic bar chart with Data[Last Date of Month] as the axis and the Start Date Table[Total enrolments in month] as the values. So far so good.

However, I then want to create a page filter with Data[Gender] and the chart doesn't respond.

Any ideas would be really appreciated.

[v-xiaoyan-msft]

Hi @JohnBSL ,

If you just want to filter your Data Table with just Data[Gender], there is a simpler way to do it.
First you can discard your second calculation table, and you can do it in Power BI Desktop using only Data Table.

You can do as the following steps：

1.Select the cluster column chart on Power BI Desktop and drag the "Client" column to the Value bar and the "Last Date of Month" column to the Axis bar.
2.drag "Gendar" to Filters on this visual.
You will get the result like below:

For more information, please see:

Add a filter to a report in Power BI 

Types of filters in Power BI reports 

If I have not understood your needs correctly, please do not hesitate to inform me.

Hope it helps,

Community Support Team _ Caitlyn Yan

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

[JohnBSL]

Hey Caitlyn,

I'm actually trying to create a running total of monthly enrolments which is why I created the second table. I was able to do this with the calculated table that I created but then I wasn't able to filter the table by gender. I've re-read my original post and it looks like I didn't make this part clear.

John.

[amitchandak]

@JohnBSL , the second calculated table will not filter using the slicer from the first table.

What is need of that.

On a visual use date from first table and count to clientId

or create a measure like this and use it with the date

measure = count(Table[ClinetID])

[JohnBSL]

I'm not sure if I explained it properly but I'm basically wanting to use the [Start Date Table] to create a list of each unique value of the last date of the month column and then calculate the number of clients with a start date in that month. My next step is to create a new column in this table that has a running total of clients. Would this still be possible if I used a single table?