Power BI Q&A Visual is return strange results.

[Arnold_Bry]

Hey guys,

I lost all my patient trying to fix bellow Q&A visual issue and need your help. 

I have a model in AAS that I can't modify directly, so i added measures for Q&A directly in report. 

In the model there are 2 tables that i use in this calculation: 

dim Calendar and fact employee:

both tables are not connected each other with any active relationship. 

I have simple measure: 

Test =

VAR MaxDate = MAX ( 'CALENDAR MASTER'[Date] )

RETURN

CALCULATE(

    COUNTROWS(EMPLOYEE),

    EMPLOYEE[Effective Start Date] <= MaxDate,

    EMPLOYEE[Effective end date] >= MaxDate

)

When I call Measure through Q&A with Year filter on slicer i got same results as for KPI visual, and everything works as it should. 

But whenever i put year filtering context into Q&A the results are going wild, e.g. 

When we compare results gruped by year in both cases we can see totaly diffrent results, and also Total for Q&A visualization is wrong (based on measure logic it should be the same number as latest year):

I check the year field and for sure the Year used for Q&A is the same field as used for grouping in table (it's working well with other fact tables that have active relationship). Also when I setup active relationship everything works fine, but every time when the filter context is setup in calculate (like in the example measure) the result is wrong. 

Have anyone idea what is causing the issue or how to fix it? I already lost all of my invention how to handle this problem.

[johnbasha33]

@Arnold_Bry 

1. Filter Propagation: Q&A visualizations may not always propagate filters correctly, especially when using measures with complex filtering logic. Try simplifying your measure to see if it resolves the issue. For example, instead of using CALCULATE, you can try using simple FILTER expressions to filter the data.

2. Context Transition: When using measures with CALCULATE, be mindful of how the filter context transitions between tables. In your case, since 'dim Calendar' and 'fact employee' are not connected, ensure that the filter context is applied correctly to both tables. You may need to use functions like CROSSFILTER or USERELATIONSHIP to establish the desired relationships between the tables within the measure.

3. Date Filtering: Check if the date filtering applied by the Year slicer is affecting the results as expected. Sometimes, unexpected results can occur if there are conflicting or overlapping date filters in the report. Ensure that the date filter applied by the slicer aligns with the date fields used in your measure and visualization.

4. Data Model: Since you mentioned that you cannot modify the data model directly, ensure that the relationships and data model structure are correctly configured to support your analysis. If necessary, you may need to work with the data model owner or administrator to make any required adjustments.

5. Testing and Validation: Validate your measure logic and visualization results by comparing them against known data points or using simple test cases. This can help identify any inconsistencies or unexpected behavior in your calculations.
   
   
   Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[amitchandak]

@Arnold_Bry , Do you have more than one column with the same name like a year? Check that out. If you have similar name Q&A might be picking up other one

[Arnold_Bry]

I checked it carefully, all other colums are turned off for Q&A and also do not have the same name or synonym.