Forum Discussion

povversas's avatar
povversas
New Member
8 years ago
Solved

Interactions between Column Chart and Table

Hello,

 

Report description:

I have Rooms data, columns: [Room],[Status],[Status Start];[Status End];

I have Date table which has NO Relationship to Rooms Table.

 

There is a measure to count Rooms in Period per Status (link):

CountPerPeriod2 = CALCULATE(COUNT(Table2[Room]);
FILTER(Table2; ([Status Start] <= LASTDATE('Date'[Date]) 
&& [Status End] > FIRSTDATE('Date'[Date]))))

 

And there are two visuals:

 

Problem and requirement:

When the specific date and status is selected in Chart, Table should show ony selected Rooms.

Now Table shows all Rooms for selected Status ignoring date filter:

I guess it is because Date and Rooms has NO Relationship.

But this measure requires NO Relationship.

Is it possible to overcome this issue?

 

  • povversas's avatar
    povversas
    8 years ago

    v-sihou-msft

    Thank you for the guidance, your comment about Visual level filter helped.

    However measure was not correct. This part in IfAvailable measure:

    CALCULATE(MAX ( Table2[Status Start] ))

    takes MAX of [Status Start], but in those cases then selected day has more than one room available MAX is wrong function to use.

     

    I found simplier solution to this. In table create Visual level filter of the main measure and set it to "is not blank":

    Solution

    Working

     

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    povversas

     

    Your measure is to calculate count of Vacant/Occupied rooms over a full calendar. It should not have relationship to Date table. So it will always ignore Date[Date] filter. In your scenario, you can create a measure to check if a room is available:

     

    IfAvailable =
IF (
    CALCULATE ( MAX ( Table[Status_Start] ) ) < MAX ( date[date] )
        && CALCULATE ( MAX ( Table[Status_End] ) ) > MAX ( date[date] ),
    0,
    1
)

    Then add a Visual Level filter on your table visual with above measure. Set IfAvailable is 1. 

     

     Regards,

    • povversas's avatar
      povversas
      New Member
      8 years ago

       v-sihou-msft

      Thank you for answer.

       

      I created new measure:

      IfAvailable = 
IF (
    CALCULATE ( MAX ( Table2[Status Start] ) ) < MAX ( 'Date'[Date] )
        && CALCULATE ( MAX ( Table2[Status End] ) ) > MAX ( 'Date'[Date] );
    0;
    1
)

      Attached Visual filter to table:

      Visual Filter

      After this I don't get desired result:

      Error in filter

       Table should show only one Row.

       

       

       

      v-sihou-msft can you recheck IfAvailable measure logic is it correct?

    • povversas's avatar
      povversas
      New Member
      8 years ago

      v-sihou-msft

      Thank you for the guidance, your comment about Visual level filter helped.

      However measure was not correct. This part in IfAvailable measure:

      CALCULATE(MAX ( Table2[Status Start] ))

      takes MAX of [Status Start], but in those cases then selected day has more than one room available MAX is wrong function to use.

       

      I found simplier solution to this. In table create Visual level filter of the main measure and set it to "is not blank":

      Solution

      Working