Drill through giving incorrect results when using measure

[anmoltripathi5]

Hi,

I have a dataset in .xlsx format containing Columns "Group", "LOB" "Function", "Headcount", "Level", "Country".

.pbix file -https://drive.google.com/file/d/12Q3_cHZQq1WCdFNSoGzSVHshy24dGUWw/view?usp=sharing

I have 4 parameters in place, If I select "India" in Parameter "Country 1" and 10 in "Headcount % Change1", It means I am increasing the Headcount of India by 10%, Which gives me headcount_New. On top of this calculation I calculated "Concentration percent New" which is the percent of total at funtion level.

Then I created "flag" measure which is based on certain conditions of "level", "Headcount_New", Concentraton Percent New" which gives result as 1 or 0.

Measure "Status" = IF([Flag] = 1, "Over", "Under")
Measure "CountIDS_over" is the Count Distinct of Functions if "Flag" =1.

I Created two worksheets in Power BI file "Page 1" and "Page 2".
"Page 1" has a bar chart with levels "A" and "B", I created the "Page 2" as drillthrough page. When I drill through at Level "B" it should be giving me correct results with Function2 and Function 7 as resulting rows since only these two rows contains "Over" status in any of the countries.
But instead of this it is giving me incorrect results with 4 Functions.

Can you please help me with creating a dax calculation or what operation do I have to perform for correct results with "over" status.

Thanks in advance!!

[ShejalTiwari]

Hi,

I have a dataset in .xlsx format containing Columns "Group", "LOB" "Function", "Headcount", "Level", "Country".

.pbix file -https://drive.google.com/file/d/12Q3_cHZQq1WCdFNSoGzSVHshy24dGUWw/view?usp=sharing

I have 4 parameters in place, If I select "India" in Parameter "Country 1" and 10 in "Headcount % Change1", It means I am increasing the Headcount of India by 10%, Which gives me headcount_New. On top of this calculation I calculated "Concentration percent New" which is the percent of total at funtion level.

Then I created "flag" measure which is based on certain conditions of "level", "Headcount_New", Concentraton Percent New" which gives result as 1 or 0.

Measure "Status" = IF([Flag] = 1, "Over", "Under")
Measure "CountIDS_over" is the Count Distinct of Functions if "Flag" =1.

I Created two worksheets in Power BI file "Page 1" and "Page 2".
"Page 1" has a bar chart with levels "A" and "B", I created the "Page 2" as drillthrough page. When I drill through at Level "B" it should be giving me correct results with Function2 and Function 7 as resulting rows since only these two rows contains "Over" status in any of the countries.
But instead of this it is giving me incorrect results with 4 Functions.

Can you please help me with creating a dax calculation or what operation do I have to perform for correct results with "over" status.

Thanks in advance!!

[v-jtian-msft]

Hi,@anmoltripathi5 .I am glad to help you.

According to your description, you have the issue when trying to do a page drilldown on measure (it makes a calculation error and returns the wrong result).

This is a relatively common problem. In fact, the calculation results of a measure are very dependent on the environment in which it is calculated (it is simply understood that the same measure is likely to be calculated in a different environment on different pages, resulting in incorrect results when you drill down to a new page that is subject to the original measure's calculation environment being changed).
For measure drilling, you must ensure that the calculation environment remains consistent before and after drilling. (Filters within the dax code, filters in the Filters area, and filters brought to the measure by fields in different visuals) will all affect the final result of the measure.

Let me start with my suggestion
Due to my work environment, I can't download the pbix file you uploaded, please upload it via OneDrive if possible.

I performed the test below:
I created test data based on your description and wrote a simulated measure.
I created two report pages, the first page (main page) has two parameters for slicer filtering, while the second page (drilldown page) does not place these two parameters slicer
like this:
page1
 

page2

As you can clearly see, due to the different calculation environments of the two pages (the measure I created needs to be filtered by the slicer)
measure:[headcount_New] and other measure calculations are inconsistent with page1 (wrong results)

This is because the slicer's filtering scope is only for the page it is on by default, and the slicer's filtering is inconsistent, resulting in different results for the two pages, even though they place the exact same fields.

The key to resolving this is to ensure that the filtering environment of the drilldown page (child page) and the original page (parent page) are consistent.
You can do this in several ways:
1. Use Synchronized Slicer (recommended)
Turn on the synchronized slicer, then hide the slicer on the child page and keep only the slicer on the main page.

2. try to modify the filters in dax to artificially enforce the range of filters in the measure via functions such as ALL()  /  ALLSELECTED() to ensure that the computational environment doesn't change.

Luckily, I found other related and resolved issues, with other users mentioning that

When using drilldown, filters created in a measure that uses CALCULATE are not passed to the drilldown page. Only the report's own filters can be passed, i.e. fields, slicers, other filters on the visual object itself.

Other users have done this by creating multiple pages for each drilldown metric to ensure a consistent calculation environment.
URL:
Solved: Drillthrough on measure with dax filter context no... - Microsoft Fabric Community
Solved: Drill through for calculated measure - Microsoft Fabric Community
Slicers in Power BI - Power BI | Microsoft Learn
 

I would rather recommend that you try using a synchronized slicer and hide away the slicer on the subpage for filter condition synchronization.
Like this:
For Home page:

For Child page:

I have uploaded the file for testing and marked all the measures used in it, hope this helps.
Here is the dax code and the test data.

Could you please try uploading the pbix file via OneDrive with the relevant screenshots, which will help resolve your issue?

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ShejalTiwari]

I uploaded the files on github as well Please check - https://github.com/Shejalt/Power-BI-files

[v-jtian-msft · Accepted solution]

Hi,@ShejalTiwari .Thank you for your reply.
like this?

Steps I tried:
Turned on the Synchronized Slicer feature for all four slicers that are common to both pages to ensure that the filters are consistent between pages
Important to note: The calculation of metrics in Power BI depends on the current filtering context.

Metrics calculation in Power BI depends on the current filtering context. If the filter conditions are not consistent between pages, the metrics may be calculated differently, resulting in inaccurate drill-down results. Use the Synchronize Slicers feature to ensure that slicer selections are synchronized across multiple pages. This ensures that the filtering context is consistent across all pages, which in turn ensures that the results of the metric value calculations are consistent.
If you don't have this feature turned on, it can result in incorrect drilling down to the MEASURE results (inconsistency of the MEASURE filtering context between two pages)

I hope my test was helpful.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ShejalTiwari]

Thanks! so much Carson Jian for the reply and explaining, suggestions and good ideas.

I checked the file sent by you "task -Copy_follow.pbix. 

I selected the above values in my parameters and tried to drill through level "A"

This gives me following result with three fucntions. Third row item shouldnt be there as no "over " status is present in third row.

Just like that, when I drill through Level B, then I should get only two rows function 2 and function 7.

[ShejalTiwari]

Can you please try downloading again using this link? - https://drive.google.com/file/d/1cByOkbLKcAqckkexhc9KvfU3OKVVV15J/view?usp=sharing

[v-jtian-msft]

Hi,@ShejalTiwari ,
I'm very sorry to say that I can't open the shared link you provided due to environmental reasons, please try to see if using the Synchronized Slicer and Hiding solves your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anmoltripathi5]

Hi @v-linhuizh-msft, facing this issue. Can you please help?