Forum Discussion

brinky's avatar
brinky
Helper IV
6 years ago
Solved

Filter table dynamically with slicers

Hello,

 

I have created this report for sales & turnover as attached, having 2 slicers with month and customer.

 

I have an excel sheet showing comments and date for the customer.

 

I would like that when Month & Customer month is selected the comment appears, and also when select all on both slicerss are chosen comment boc will be empty.

 

Also would such comment will be possible to place on a card instead of a table?

 

Thanks in advance.

 

 

Need Help
  • RMB's avatar
    RMB
    6 years ago

    Yes, this should be doable, what you need to do is create a Measure that checks if those two columns (date and customer) are filtered and if they are select the comment otherwise show blank.

     

    Measure structure should resemble:

    Comment_Measure =
IF( ISFILTERED(INSERT DATE COLUMN), //Date filter check.
       IF( ISFILTERED(INSERT CUSTOMER COLUMN), //Customer filter check.
           MAX(INSERT COMMENT COLUMN), //Both were filtered.
           Blank() //Date was filtered but Customer wasn't, you can change this to a default 
                       //message like "Select Date & Customer for Comment"
       ),
       Blank() //Date wasn't filtered, you can change this to a default 
                   //message like "Select Date & Customer for Comment"
)

    Now drop that Measure into the card. Keep in mind that this is assuming you only have one comment for a customer in a month, you would need to add more logic to grab a list for example if you have more.

3 Replies

  • RMB's avatar
    RMB
    Resolver II
    6 years ago

    Yes, this should be doable, what you need to do is create a Measure that checks if those two columns (date and customer) are filtered and if they are select the comment otherwise show blank.

     

    Measure structure should resemble:

    Comment_Measure =
IF( ISFILTERED(INSERT DATE COLUMN), //Date filter check.
       IF( ISFILTERED(INSERT CUSTOMER COLUMN), //Customer filter check.
           MAX(INSERT COMMENT COLUMN), //Both were filtered.
           Blank() //Date was filtered but Customer wasn't, you can change this to a default 
                       //message like "Select Date & Customer for Comment"
       ),
       Blank() //Date wasn't filtered, you can change this to a default 
                   //message like "Select Date & Customer for Comment"
)

    Now drop that Measure into the card. Keep in mind that this is assuming you only have one comment for a customer in a month, you would need to add more logic to grab a list for example if you have more.

    • brinky's avatar
      brinky
      Helper IV
      6 years ago

      RMB Wow works like a charm, thanks a millon.

       

      I'm asking as you got me into it, what if I have 2 or more comments for a customer in a month :smileyhappy:

       

      I'm asking as if it's not that complicated I will do just in case such event will happen.

       

      Thanks once agian.

       

      Stephen

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        You can you use the following code instead of the max() :

        CONCATENATEX(VALUES(Fact_Table[Column]);Fact_Table[Columnt]; " - ")