Trying to show all values in table visual from another table that accounted for calculated column

[kundrapakammano]

• Here is the month table.In "month table" i created calculated column "count2" using the dax below. This dax uses the "table1" columns to calculate values based on "End Of Month" column.
  

  month table.

  

Dax i used for caculating Count2:

Count2 = if(MonthTable[End Of Month]<=TODAY(),CALCULATE(COUNT('table1'[Owning Location]),FILTER('table1','table1'[opened_dt]<=MonthTable[End Of Month] && 'table1'[closed_dt]>MonthTable[End Of Month] && 'table1'[Included in KPI On Time]=TRUE()))+CALCULATE(COUNT('table1'[Owning Location]),FILTER('table1','table1'[opened_dt]<=MonthTable[End Of Month] && 'table1'[closed_dt]=BLANK() && 'table1'[Included in KPI On Time]=TRUE())) ,0)

I am trying to show all the rows in visual from "table1" that accounted for value "Count2" in "month table".

Note: There is no relationship between "table1" and "month table". "month table" is for calculating the values for every month end date(count1 and count2)

when user clicks on the values in above visual ,I am trying to get the values from "table1" that accounted for "Count2" in table view.(sample columns from "table1")

[fhill]

This is why your click filter probably isn't working?

  There is no relationship between "table1" and "month table".

If there's no relationship between Table 1 and the Date Table, your DAX can read Table 1, but there's no way for a visual to 'remember' a value clicked and re-apply it back to a new table without a relationship?

Forrest

[kundrapakammano]

I attached the sample file, may be it will give you a overview what iam trying to achieve.Thanks

[Icey · Accepted solution]

Hi @kundrapakammano ,

Try this:

1. Create a measure.

Measure =
VAR SelectedEndofMonth =
    SELECTEDVALUE ( MonthTable[End Of Month] )
RETURN
    IF (
        SelectedEndofMonth = BLANK (),
        1,
        IF (
            SELECTEDVALUE ( 'Table'[opened_dt] ) <= SelectedEndofMonth
                && (
                    SELECTEDVALUE ( 'Table'[closed_dt] ) > SelectedEndofMonth
                        || SELECTEDVALUE ( 'Table'[closed_dt] ) = BLANK ()
                )
                && SELECTEDVALUE ( 'Table'[Included in KPI On Time] ) = TRUE (),
            1
        )
    )

2. Put this measure into "Filters on this visual" field of your table visual and set as "is not blank".

3. Test.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@kundrapakammano , Your expected output is not clear. Based on the data you shared.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

if you want deal with more than one date refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[kundrapakammano]

 i am trying to filter table values based on visual.

i am trying to filter values in table based on bars in top visual

https://drive.google.com/file/d/1csFAvXqKZreyiKJKCf1apODNvAFHKdeF/view?usp=sharing