CalculateTable dynamically based on Report Filters

[Jig_S]

Hello,

We are trying to create a report to be later embedded into our website/portal. However, for this report we need the difference between two tables. The two tables are the same except one need to be static, and a duplicate table needs to change dynamically based on Report Level filter, or the filter passed as a URL when embedding the report using PBI embedded. 

We tried a lot of different variations of CALCUATETABLE and FILTER to make the table dynamic based on the Report Level Filters.

Is there a way in PowerBI to do it so that we can then use the EXCEPT function to get the difference between two tables ?

Any appreciate any help in this matter.

[v-qiuyu-msft]

Hi @Jig_S,

How did you build the relationship between those two tables? Are those two tables linked or independent? I would suggest you share some sample data and screenshots about desired results for our analysis.

Best Regards,
Qiuyun Yu

[Jig_S]

Hello Qiuyun_Yu,

The tables are related to each other. Unfortunately I cannot share data/snaps but here's a sample process: Table A (Client Table) is linked to Table B. So, when I select a client in Table A, it will cross filter related entries in Table B. 

What we want is all the entries other than the entries for the selected Client. To do this, we created a duplicate of Table B (Call it Table C) , and did not link it to any other Table. 

Now, we want to use the EXCEPT function to get the difference between Table C and Table B, when a particular client is selected in the slicer/filter/Passed Through URL.

The problem is when we use CALCULATETABLE or FILTER, it only allows to FILTER on a value and cannot accept a column of Table. Ideally we could pass the client table column in CALCULATETABLE/FILTER and use the client Table in slicer which would solve the problem, but I guess it is not supported out-of-the-box.

[Habib]

Have you tried SUMMARIZE funtion to summaries the table based on filters?

[Jig_S]

Hi Habib,

Thanks a lot for your reply. Yes we tried SUMMARIZE, but didn't achieve what we intend to. I believe what we want to achieve is linking any SUMMARIZE/CALCULATE functions for table calculations with values selected from Report Level Filters or Filters passed in URL when embedded in a Webpage. 

That makes the Calculated Table dynamic with respect to the selected filter. So if we have Client A selected, the Calculated Table will filter dynamically on Client A, same thing for Client B ,etc.

Best Regards,

[CheenuSing]

Hi @Jig_S

Is it possible to share some data and visuals you are expecting so as to think a solution.

Cheers

CheenuSing