Reactive fact table when i clicked the bar of chart, made of 3 different dates.

[CrouchingTiger]

Hi, experts.

I tried and searched, but I can find the solution.

I have fact table, it shows items about project.

Like id, title, teamname, assigned to, startdate, enddate, ceated, status...

And 2 charters, one is clutered column, another is line with clustered column chart.

Slicer settled with date of calendar table.

And calendar table [date] got a relationship with created date of origin table. Activated

Start date, end date are got a relationship with calendar date, too, but not activated.

Made some measures for count of items of each dates( created, start, end )

Charter has those 3 measures.

The table basically shows items(records:id,title,status...) based on created date and the chart shows count of created, start, end date items on bar

If I clicked the one of three bars on chart, then I want to show items on table that I clicked.

How can I make it?

Best regards.

[v-sdhruv]

Hi @CrouchingTiger ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @CrouchingTiger ,

Thanks for clarifying.

I believe you already have created three separate DAX measures to count the items based on CreatedDate, StartDate, and EndDate.

Example:
CreatedCount = COUNTROWS(FILTER(FactTable, FactTable[CreatedDate] IN VALUES(Calendar[Date])))
and similarly for start date and End Date

To dynamically filter the table based on which bar is selected in the chart, you need to create filter measures that will act based on the clicked bar.

FilterCreated =
IF(
ISFILTERED(FactTable[CreatedDate]),
1,
0
)

Similarly for End Date and Start Date.

Add these Filter Measures (FilterCreated, FilterStart, and FilterEnd) to the "Filters on this visual" pane for the table visual.

Set the filter condition for each measure to show only when the value is 1.
This will filter your table dynamically.

Hope this helps!

[CrouchingTiger]

If I apply this method, I will need to change the value in the filter every time.
Because I have to change the value in the filter after choosing from the charter. It doesn't come out right away...

[v-sdhruv]

Hi @CrouchingTiger ,

Yes it is possible to show your table interacting with your bar graphs given your scenario.
Since your create Date, Start Date and End date are in the same table, anything you filter, will show with respect to these fields.
In your case,
Department on X-axis with count of create date, Start date and End date on Y-axis, you get the ability to select on a particular department not on any create date/Start date/End date because Department is on x-axis.

So if you click on any bar the table will show all the items including start date/End date/Create date filtered for that department.

If you wish to create separate tables for end date/create date you can implement it through drill- through page.

Learn more about drill-through here-

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

Hope this helps!

[CrouchingTiger]

Thank you for reply.

Drill-through indicates page movement.
I want to make the list of items appear in table visual if I choose a bar that is not page movement.

What I’m trying to achieve is this:

I have a relationship between the Date column in the Calendar table and the Created date in the Fact table.
In addition, I’ve set up inactive relationships between the Calendar table's Date column and the StartDate and EndDate fields in the Fact table,  for using USERELATIONSHIP.

I've created three separate measures to calculate the item count based on Created, StartDate, and EndDate.

Now, I’ve built the following visuals:

1. A bar chart with the department from the Fact table on the X-axis, and the three item count measures (based on Created, StartDate, and EndDate) on the Y-axis.

2. A bar chart with YearMonth from the Calendar table on the X-axis, and the same three item count measures on the Y-axis.

3. A table visual displaying columns such as ID, Title, Status, Department, Created, StartDate, EndDate from the Fact table.

What I want is:
When I click on a bar in either of the bar charts, the table visual should dynamically update to show the items corresponding to that selection.

My question is: Is this possible?

Figure1. Date slicer

Figure2. clustered column chart

What I expect is that when a bar representing the item count based on EndDate is selected in the chart, the table visual should display only the list of items corresponding to that EndDate selection.

Figure3. Table visual

If I don't select anything, I expect the table to show items based on the Created date by default.
However, when I select a specific bar in the chart from Figure 2, I expect the table to update and show items based on that selected bar’s context.

[techies · Accepted solution]

Hi @CrouchingTiger have you tried creating disconnected table?

DateTypeSelector = DATATABLE(

    "DateType", STRING,

    {

        {"Created"},

        {"Started"},

        {"Ended"}

    }

)

Dynamic Date Count measure=

SWITCH(

    SELECTEDVALUE(DateTypeSelector[DateType]),

    "Created", [Items Created],

    "Started", [Items Started],

    "Ended", [Items Ended]

)

[CrouchingTiger]

I'm not looking at it by DateType through Slicer, but I'm adopting it because it's the best answer.I'm not looking at it by DateType through Slicer, but I'm adopting it because it's the best answer.
The current solution was solved without slicer by Pivoting Created, StartDate, and EndDate to create DateType and Value for each itemcode and dividing DateType into Legend.
The clustered column chart visual does not come out as the UI you want, but I'm satisfied with the features I want to see.

[CrouchingTiger]

Thank you for your kind explanation.

What I’m trying to achieve is this:

I have a relationship between the Date column in the Calendar table and the Created date in the Fact table.
In addition, I’ve set up inactive relationships between the Calendar table's Date column and the StartDate and EndDate fields in the Fact table,  for using USERELATIONSHIP.

I've created three separate measures to calculate the item count based on Created, StartDate, and EndDate.

Now, I’ve built the following visuals:

1. A bar chart with the department from the Fact table on the X-axis, and the three item count measures (based on Created, StartDate, and EndDate) on the Y-axis.

2. A bar chart with YearMonth from the Calendar table on the X-axis, and the same three item count measures on the Y-axis.

3. A table visual displaying columns such as ID, Title, Status, Department, Created, StartDate, EndDate from the Fact table.

What I want is:
When I click on a bar in either of the bar charts, the table visual should dynamically update to show the items corresponding to that selection.

My question is: Is this possible?

Figure1. Date slicer

Figure2. clustered column chart

What I expect is that when a bar representing the item count based on EndDate is selected in the chart, the table visual should display only the list of items corresponding to that EndDate selection.

Figure3. Table visual

If I don't select anything, I expect the table to show items based on the Created date by default.
However, when I select a specific bar in the chart from Figure 2, I expect the table to update and show items based on that selected bar’s context.

[bhanu_gautam]

@CrouchingTiger  Ensure you have measures for counting items based on created, start, and end dates. It seems you already have these measures.

You mentioned that the calendar table has relationships with created, start, and end dates in the fact table. Ensure these relationships are correctly set up, with the created date relationship being active and the others inactive.

You have a slicer based on the calendar table's date field. This slicer will filter the data based on the selected date range.

To filter the table based on the selected bar in the chart, you need a combined measure that dynamically adjusts based on the selected bar. Here’s an example of how you can create such a measure:

DAX
SelectedItems =
VAR CreatedCount = COUNTROWS(FILTER(FactTable, FactTable[CreatedDate] IN VALUES(Calendar[Date])))
VAR StartCount = COUNTROWS(FILTER(FactTable, FactTable[StartDate] IN VALUES(Calendar[Date])))
VAR EndCount = COUNTROWS(FILTER(FactTable, FactTable[EndDate] IN VALUES(Calendar[Date])))
RETURN
CreatedCount + StartCount + EndCount

Add the SelectedItems measure to the table visual. This measure will dynamically update based on the selected bar in the chart.

Ensure that the interactions between the chart and the table are set up correctly. To do this:

Click on the chart.
Go to the "Format" tab.
Select "Edit Interactions".
Ensure that the table visual is set to filter based on the chart selection.

[CrouchingTiger]

Thank you for reply.

What i want to do is, show items into table visual. Based the bar that I clicked.

Ex) id, title, teamname, assigned to, startdate, enddate, ceated, status...

Add the SelectedItems measure to the table visual => It means add to field? or filter on this visual of table?

I'm not tried yet but table visual has lots of fields(id, title, teamname, assigned to, startdate, enddate, ceated, status...)

And your measure looks like sum of count items.