Problem with the filter on a comparison chart whose data comes from merged tables

[Patrick_Rumya]

I'm creating a report with several charts using Power BI. In my main table or query, I have two columns (besoins_baseline and besoin_pdm) that I'd like to compare based on the number of times a value appears. The possible values ​​in the two columns are: Plupart, ---, Aucun, Certains, Tous. The data comes from Comcare HQ

To do this, I grouped each column by the number of times the value appears, and here are the results of two queries generated by the grouping action:

Next, I merged the two tables mentioned above to compare the results on a single Power BI visual using this query. The generated chart looks like the image below, and so far it works well; the comparison between Baseline and PDM is effective.

The problem I'm having is that my report contains many other visuals, and I need to apply different data filters depending on the user's needs (for example, by date, city, respondent's gender, etc.). Applying the different filters works fine on the other charts in the report, but for the chart above, the information doesn't change when I select any filter.
I understand that this might be because the queries produced separate tables, meaning the filter elements don't come from the table that was merged to allow for comparison.
Therefore, I need help with a procedure I can apply to perform this comparison so that, ultimately, the values ​​in this chart change according to the filters selected in the report. I tried using parameters and some DAX formulas, but it didn't achieve my goal. Perhaps I applied it incorrectly, as I'm not yet very familiar with Power BI.
Thank you for your help, which will be greatly appreciated.

[v-priyankata]

Hi @Patrick_Rumya 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@MFelix @Ashish_Mathur Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @Patrick_Rumya 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[Ashish_Mathur]

Hi,

Assuming besoins_baseline is a column in other Fact tables as well, create a table of unique entries which exist in the first column of both tables (shown in your image).  Build a Many to One relationship from the fact tables to this newly created dim table.  Do not merge the two tables.

[MFelix]

Hi @Patrick_Rumya ,

For this you need to create a table with the besoins unique values and then use two dax measures on top of your data without the group in Power Query that will count the values.

This calculations can be done with or without relationship between your table and the besoins table. Below see an example of calculations without relationship:

Baseline Values = CALCULATE(COUNTROWS('Fact Table'), TREATAS(VALUES(Besoins[Besoins]), 'Fact Table'[Besoins Baseline]))

PDM Values = CALCULATE(COUNTROWS('Fact Table'), TREATAS(VALUES( 'Besoins'[Besoins]), 'Fact Table'[Besoins PDM]))

Final Result:

The disconnected table must be used has the Y-axis, in this example and the measures has values, any filter you apply to the report will be applied to this calculation.

If you want you can make relationships between the tables and then use a similar sintax to this one:

Baseline Values = CALCULATE(COUNTROWS('Fact Table'), USERELATIONSHIP(Besoins[Besoins], 'Fact Table'[Besoins Baseline]))

PDM Values = CALCULATE(COUNTROWS('Fact Table'), USERELATIONSHIP( 'Besoins'[Besoins], 'Fact Table'[Besoins PDM]))

Result is the same.