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

[ashishd]

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

[Anonymous · Accepted solution]

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]

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!

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

INDEX function (DAX) - DAX | Microsoft Learn