Forum Discussion

User232431's avatar
User232431
Helper III
1 year ago
Solved

Relationship between two table not working after calculating Last N months

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))
*****
Need Help
  • User232431's avatar
    User232431
    1 year ago

    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).

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    1 year ago

    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

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      User232431
      Helper III
      1 year ago

      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).

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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