Forum Discussion

Hennadii's avatar
Hennadii
Icon for Helper IV rankHelper IV
6 years ago
Solved

Filter table based on related table with inactive relation

Initial Data A Data Model with relations and draft of a Report Page are attached.     Target When a Period selected in a slicer (from a List), the Created Actions and Closed Actions ...
Need Help
  • Hennadii's avatar
    Hennadii
    6 years ago

    Thank you all for assistance amitchandak , parry2k , Anonymous 


    I've got the solution!
    I'll leave it here in case some one other faced the same situation:

     

    I proceeded with solution to create intermediate table as following

    Dates and Actions = 
VAR _calendar = 
    CALENDAR( MIN( 'Actions'[Created On]), MAX( 'Actions'[Closed On] ) )
RETURN
    SELECTCOLUMNS(
        FILTER (
            CROSSJOIN('Actions', _calendar),
            'Actions'[Created On] = [Date] ||
            'Actions'[Closed On] = [Date]
        ),
        "Action ID", 'Actions'[Action ID],
        "Date", [Date],
        "Event" , IF('Actions'[Created On] = [Date], "Created", "Closed" )
    )

    The Dates and Actions table should has relations as in the post above.

    In a Report Page needed to:

    1. Drop column Action ID from the Dates and Actions table;
    2. Make sure that it appears as Table;
    3. Expand Filter pane and drop Event from the Dates and Actions table into "Add data fields here" section;
    4. Make a copy of the table;
    5. Name one table as Created Actions and select Created in the Filter pane;
    6. Name another table as Closed Actions and select Closed in the Filter pane.

     

     

    After that, by selecting a Period in the slicer, only Created and Closed Actions within the period will be listed in two tables.

    P.S. I'm sure, there should be a way to solve it using inactive relations without creating additional Dates and Actions table.

    I'm sure about that, because if set active relation between Actions[Created On] and Calendar[Date] (see Data Model in the first post), the Actions table in a Report Page is filtered to only Created actions for the selected Period.

    And it can be filtered to show only Closed actions if change active relation to Actions[Closed On] and Calendar[Date]

    Unfortunatelly, there is no way to keep both relations as active ...

    I'll give kudo and set "Accept as Solution" if someone propose how to do it through inactive relations.

Hennadii's avatar
Hennadii
Icon for Helper IV rankHelper IV
6 years ago

Hi Anonymous , that could be a way to solution. But it does not give me expected result for now.


I created additional table as you suggested in the post you mentioned.

Dates and Actions = 
VAR _calendar = 
    CALENDAR( MIN( 'Actions'[Created On]), MAX( 'Actions'[Closed On] ) )
RETURN
    SELECTCOLUMNS(
        FILTER (
            CROSSJOIN('Actions', _calendar),
            'Actions'[Created On] <= [Date] &&
            'Actions'[Closed On] >= [Date]
        ),
        "Action ID", 'Actions'[ID],
        "Date", [Date]
    )

Here attached updated Model. One note to dates:

  • Start Date and End Date in Period table do not overlap each other --> 1-1 relation between Calendar and Dates and Periods. 
  • Created On and Closed On in Actions table can overlap each other --> 1-* relation between Calendar and Dates and Actions. 

 

When I place Action ID from Dates and Actions into Report Page (in a Table view), and select a Period from a slicer, it shows all Actions ID which overlap Start / End Dates of the selected Period.

In that case if Action 1 has dates 01/01 - 01/30 and Period dates  01/10 - 01/20, the Action 1 will be shown in the results but it is not correct as I need to see only Actions which were Closed or Created in the selected Period.

 

Hennadii's avatar
Hennadii
Icon for Helper IV rankHelper IV
6 years ago

Thank you all for assistance amitchandak , parry2k , Anonymous 


I've got the solution!
I'll leave it here in case some one other faced the same situation:

 

I proceeded with solution to create intermediate table as following

Dates and Actions = 
VAR _calendar = 
    CALENDAR( MIN( 'Actions'[Created On]), MAX( 'Actions'[Closed On] ) )
RETURN
    SELECTCOLUMNS(
        FILTER (
            CROSSJOIN('Actions', _calendar),
            'Actions'[Created On] = [Date] ||
            'Actions'[Closed On] = [Date]
        ),
        "Action ID", 'Actions'[Action ID],
        "Date", [Date],
        "Event" , IF('Actions'[Created On] = [Date], "Created", "Closed" )
    )

The Dates and Actions table should has relations as in the post above.

In a Report Page needed to:

  1. Drop column Action ID from the Dates and Actions table;
  2. Make sure that it appears as Table;
  3. Expand Filter pane and drop Event from the Dates and Actions table into "Add data fields here" section;
  4. Make a copy of the table;
  5. Name one table as Created Actions and select Created in the Filter pane;
  6. Name another table as Closed Actions and select Closed in the Filter pane.

 

 

After that, by selecting a Period in the slicer, only Created and Closed Actions within the period will be listed in two tables.

P.S. I'm sure, there should be a way to solve it using inactive relations without creating additional Dates and Actions table.

I'm sure about that, because if set active relation between Actions[Created On] and Calendar[Date] (see Data Model in the first post), the Actions table in a Report Page is filtered to only Created actions for the selected Period.

And it can be filtered to show only Closed actions if change active relation to Actions[Closed On] and Calendar[Date]

Unfortunatelly, there is no way to keep both relations as active ...

I'll give kudo and set "Accept as Solution" if someone propose how to do it through inactive relations.