Relationship between two table not working after calculating Last N months

[User232431]

Hi I am trying to buld relationship between two table for common variable after calculating last N months / N weeks / N years

Once i create relationship(With Location,Frequency,Period columns form 2 tables) , graph that is responding only through Filter "Table" Slicer but not with "Data" table Slicers.

How to Make Graph responds both with "Data" & "Filter" Table's slicers?

Please find the below Screenshots.

The measures I wrote in "Data" table to find Last N perios is as follows:

filter =

VAR _country =

    SELECTEDVALUE ( 'Filter'[Location] )

VAR _frequency =

    SELECTEDVALUE ( 'Filter'[Frequency] )

VAR _period =

    SELECTEDVALUE ( 'Filter'[Period] )

VAR _date =

    SELECTEDVALUE ( 'Data'[Date] )

VAR _maxDate =

    CALCULATE ( MAX ( 'Filter'[Date] ) )

VAR _previousYear = 3

VAR _minDate =

    CALCULATE (

        MIN ( 'Data'[Date] ),

        FILTER (

            ALL ( 'Data' ),

            'Data'[Period]

                IN ALLSELECTED ( 'Filter'[Period] )

                    && 'Data'[Location]

                        IN ALLSELECTED ( 'Filter'[Location] )

                            && 'Data'[Frequency] IN ALLSELECTED ( 'Filter'[Frequency] )

        )

    )

VAR _minDate_before_3Month =

    CALCULATE (

        DATE ( YEAR ( _minDate ), MONTH ( _minDate ) - 3, DAY ( _minDate ) )

    )

VAR _minDate_before_3Year =

    CALCULATE (

        DATE ( YEAR ( _minDate ) - _previousYear , MONTH ( _minDate ), DAY ( _minDate ) )

    )

RETURN

SWITCH(

    SELECTEDVALUE('Filter'[Frequency]),

    "Year",

    IF (

        (

            _date >= _minDate_before_3Year

                && _date <= _maxDate )

                && SELECTEDVALUE ( 'Data'[Location] ) = _country

                && SELECTEDVALUE ( 'Data'[Frequency] ) = _frequency

        ,

        1

    ),

    IF (

        (

            _date >= _minDate_before_3Month

                && _date <= _maxDate )

                && SELECTEDVALUE ( 'Data'[Location] ) = _country

                && SELECTEDVALUE ( 'Data'[Frequency] ) = _frequency

        ,

        1

    ))

Average Value = CALCULATE(AVERAGE('Data'[Value]),FILTER('Data',[filter] = 1))

*****

[User232431 · Accepted solution]

Thank you , My desired output that i am struggling is , when i select all the 3 slicers from Table "Data" , then the graph should display the values (Because i need to display few other values from "Data" table).

[v-yaningy-msft · Accepted solution]

Hi, @User232431 

Sry, my mistake, posted the wrong pbix. You can check the pbix file below to see if it meets your needs.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-yaningy-msft]

Hi, @User232431 

If the interaction can be successful, what kind of desired effect do you want to achieve, either a text description or a picture.

Best Regards,
Yang
Community Support Team

[User232431 · Accepted solution]

Thank you , My desired output that i am struggling is , when i select all the 3 slicers from Table "Data" , then the graph should display the values (Because i need to display few other values from "Data" table).

[v-yaningy-msft]

Hi, @User232431 

Creating a relationship between these two tables won't work here, the two Slicers will interact with each other to filter the results resulting in no data in the visual object. To display other data, you can drag and drop fields directly into the table visual object.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[User232431]

Hi , Can i expect a sample PBIX file as i have some urjent requirement

[v-yaningy-msft]

Hi, @User232431 

Here is the pbix.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[User232431]

Hi Thanks for your quick reply , My requirement is that if i select slicers from table "Data" then the graph should update and not from slicers from filter table. Because i have other cards which depends on same slicers from  "Data" table . 

[v-yaningy-msft · Accepted solution]

Hi, @User232431 

Sry, my mistake, posted the wrong pbix. You can check the pbix file below to see if it meets your needs.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[User232431]

Hi ,  If i select multiple country's the graph shows as empty values , how can i get values with average for both countrys if select all. Thank you

[User232431]

Amazing , Workinig Fine , But How can you achived this - Measure (i.e Steps).

[v-yaningy-msft · Accepted solution]

Hi, @User232431 

Just create a blank table(Do not add any data), add your measure below this table and delete other filed(Column1).

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum