Forum Discussion

Kalachuchi's avatar
Kalachuchi
Icon for Helper III rankHelper III
5 years ago
Solved

Dynamic Commentary based from column and slicer

I have two tables which are connected via unique ID

UniqueIDYearNameSales
12019Arthur Morgan2
22020James Morgan45

 

Slicer

UniqueIDRegion
1OC
2ASEAN

 

So when the user has clicked OC in slicer the commentary will be "In 2019, Arthur Morgan from OC region has a sale count of 2"

then if ASEAN is clicked "In 2020, James Morgan from ASEAN region has a sale count of 45"

 

I have no idea if this is possible or not, Any help will greatly be appreciated.

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Kalachuchi  ,

    Here are the steps you can follow：

    1. Create calculated column.

    Comment =
var _OC=CALCULATE(MAX('Table (2)'[Region]),FILTER('Table (2)',[UniqueID]=EARLIER('Table'[UniqueID])))
return
"In "&[Year]&", "&[Name]&" from "&_OC&" region has a sale count of "&[Sales]

    2. Result

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Kalachuchi  ,

    Here are the steps you can follow：

    1. Create calculated column.

    Comment =
var _OC=CALCULATE(MAX('Table (2)'[Region]),FILTER('Table (2)',[UniqueID]=EARLIER('Table'[UniqueID])))
return
"In "&[Year]&", "&[Name]&" from "&_OC&" region has a sale count of "&[Sales]

    2. Result

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Kalachuchi's avatar
      Kalachuchi
      Icon for Helper III rankHelper III
      5 years ago

      Is it possible to add if the user did not clicked any slicer it will give me a static text with the total number of sales?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Kalachuchi  ,

    Here are the steps you can follow：

     1. Create measure.

    Measure =
var _select =SELECTEDVALUE('Table (2)'[Region])
var _OC=CALCULATE(MAX('Table (2)'[Region]),FILTER('Table (2)',[UniqueID]=MAX('Table'[UniqueID])))
var _amount=SUM('Table'[Sales])
return
IF(_select=MAX('Table (2)'[Region]),
"In "&MAX('Table'[Year])&", "&MAX('Table'[Name])&" from "&_OC&" region has a sale count of "&MAX('Table'[Sales]),"The total amount of sales in two years is"&" "&_amount&"")

     2. Result.

     

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.