Forum Discussion

AlexKahler's avatar
AlexKahler
New Member
6 years ago
Solved

Filtering a table in a cascading relationship

Hi everyone,

 

I'm having issues with filtering a table in a cascading relationship.

E.g., Table A has a slicer with dates and a 1-* relationship to table B. Table B has dates and id, with a 1-* relationship to Table C. Table C has ids. I want to filter C based on a date-range in table A.

 

How can this be done? When I try, my visualizations are all blank.

 

 

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  AlexKahler 

    From your screenshot of relationship, it should works well.

    I think the problem should be that

    Master Date table is used DAX, so it will only have 12:00:00AM time for each date,

    [Visit date & time] must have different time not just 12:00:00AM time for each date,

    you just change the format of them to make them same.

    So although you have create a relationship between them, but they are not same for each date.

     

    Just add a date column for [Visit date & time]

    Date = DATE(YEAR([Visit date & time]),MONTH([Visit date & time]),DAY([Visit date & time]))

    Then use this column to create a relationship instead of [Visit date & time].

    Now it should work well.

     

    Regards,

    Lin

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    AlexKahler , this relationship seems correct.  This should give data.

    Can you share sample data and sample output in table format?

    • AlexKahler's avatar
      AlexKahler
      New Member
      6 years ago

      Unfortunately, I can't as the data is NDA.

       

      I've taken a new screenshot of the actual relationship in Power BI.

       

       

      This is what happens, when I filter the report. I made sure that the datapoints for the visualizations are the same tables as above.

      And, in the tables, I can see that the date range is correct;
      Here's from Visit Info;

      And in Master Date;

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi  AlexKahler 

        From your screenshot of relationship, it should works well.

        I think the problem should be that

        Master Date table is used DAX, so it will only have 12:00:00AM time for each date,

        [Visit date & time] must have different time not just 12:00:00AM time for each date,

        you just change the format of them to make them same.

        So although you have create a relationship between them, but they are not same for each date.

         

        Just add a date column for [Visit date & time]

        Date = DATE(YEAR([Visit date & time]),MONTH([Visit date & time]),DAY([Visit date & time]))

        Then use this column to create a relationship instead of [Visit date & time].

        Now it should work well.

         

        Regards,

        Lin