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 c...
  • 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

     

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