Forum Discussion

ashishd's avatar
ashishd
Icon for Advocate II rankAdvocate II
2 years ago
Solved

Q&A visual setup- how to define an operation like difference from, change from etc.?

Hello friends,

 

I was wondering if there is any way to define an operation for PowerBI Q&A visual to consider when questions like below are asked?

What is the change in sales on the latest date compared to the previous date?

PowerBI either shows only the sales on the latest date or sales on all the dates (or sales on both the latest 2 dates with some more filtering) but never shows the difference in the sales values on those 2 latest dates.
I was not able to define "change", "difference" for PowerBi to do the required calculations in the Q&A setup window.

 

Please share any guidance on this.

Thank you!
Ashish

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ashishd 

     

    Jihwan_Kim Thank you very much for your prompt reply and here allow me to share some of it.

     

    It sounds like you want the Q&A feature to understand and calculate the difference in sales between two dates, specifically the latest date and the previous date.

     

    An effective way to deal with this issue is to create a measure in the Power BI model that is used to calculate the difference in sales between the latest date and the previous date.

     

    Once you have this measure, you can ask the Q&A visual object to display the measure.

     

    Diff = 
var _maxdate = MAX('Sheet1'[Date])
var _PreviousDate = CALCULATE(SUM(Sheet1[Metric]), FILTER(ALL('Sheet1'), 'Sheet1'[Date] = _maxdate - 1))
var _LastDate = CALCULATE(SUM(Sheet1[Metric]), FILTER(ALL('Sheet1'), 'Sheet1'[Date] = _maxdate))
return _LastDate - _PreviousDate

     

    While you cannot directly define operations like "change" or "difference" in the Q&A setup window, you can use it to define synonyms for measures or columns that might help guide the Q&A feature towards the pre-calculated measure you've created.

     

    You can ask as suggested by Q&A.

     

    Here is the result.

     

     

     

    Use Power BI Q&A to explore and create visuals in your reports - Power BI | Microsoft Learn

     

    Regards,

    Nono Chen

    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
    2 years ago

    Hi ashishd 

     

    Jihwan_Kim Thank you very much for your prompt reply and here allow me to share some of it.

     

    It sounds like you want the Q&A feature to understand and calculate the difference in sales between two dates, specifically the latest date and the previous date.

     

    An effective way to deal with this issue is to create a measure in the Power BI model that is used to calculate the difference in sales between the latest date and the previous date.

     

    Once you have this measure, you can ask the Q&A visual object to display the measure.

     

    Diff = 
var _maxdate = MAX('Sheet1'[Date])
var _PreviousDate = CALCULATE(SUM(Sheet1[Metric]), FILTER(ALL('Sheet1'), 'Sheet1'[Date] = _maxdate - 1))
var _LastDate = CALCULATE(SUM(Sheet1[Metric]), FILTER(ALL('Sheet1'), 'Sheet1'[Date] = _maxdate))
return _LastDate - _PreviousDate

     

    While you cannot directly define operations like "change" or "difference" in the Q&A setup window, you can use it to define synonyms for measures or columns that might help guide the Q&A feature towards the pre-calculated measure you've created.

     

    You can ask as suggested by Q&A.

     

    Here is the result.

     

     

     

    Use Power BI Q&A to explore and create visuals in your reports - Power BI | Microsoft Learn

     

    Regards,

    Nono Chen

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

     

     

     

  • ashishd's avatar
    ashishd
    Icon for Advocate II rankAdvocate II
    2 years ago

    Thank you Jihwan_Kim  and sincere apologies for not being very clear on my question. The solution provided by you is really useful in a different use-case but what I meant to know was about the Q&A setup in PowerBI which lets the user ask questions in Natural language.
    I am interested in understanding whether it can handle only very simple questions or can answer questions like 
    "What is the difference (change) in the metric for each item on the latest date as compared to the previous date?

    I have not been able to make PowerBI Q&A setup so that it understands the term change/difference, compared to.

    As I am not able to see an attachment button, here is the shared link for the files:
    https://drive.google.com/drive/folders/1HhvCnerbfmlY6ZvljmTC6H94o75fhyJU?usp=sharing

    Please let me know if I am clear and if there is a better way to work with PowerBI Q&A.

    Thank you!