Custom table with dynamic data

Question

I have a report with 4 slicers a date range and 3 others

I have got a visual that is linked to those slicers and it is working correctly.

I need a summary report in which i want to show custom data about count of category with and without filters but date applied.

e.g.

Category1 within applied filters 10

Category1 outside applied filters 1

Category2 within applied filters 5

Category2 outside applied filters 0

I can only do within applied filters, how can i show both with filters and without filters data?

Is there a way I can do sql query and read the date filter from the slicer and do my own sql query? just a thought.

Thanks

Talha Ahmed

v-yulgu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
Do you want the data displayed in visual not to be affected by some specific slicers? If so, you can turn off the interaction between slicers and the visual, please see&nbsp;Visualization interactions in a Power BI report
&nbsp;
If this doesn't meet your requirement, please provide sample data and show us desired output.&nbsp;How to Get Your Question Answered Quickly
&nbsp;
Best regards,
Yuliana Gu

anonymous · Answer

Thanks for the response, yes I know about visual interactions - it would rather interact or not. Can in one table I can show the data about filtered and non filtered records?

If you see the example output i have provided in my original question, is that two rows in the table should come after the filter applied and two without. I can do that into two separate tables but the calculation won't be correct, is there a way to do that in one table?

If this is not possible, is there a way i can run a dymamic query use the value from the slicers?

Hope it is much clearer now.

Thanks

anonymous · Answer

And also to add to the above that the the count should be calculated based on the data within filter and outside filter.&nbsp;For example:&nbsp;Category 1 count within filter&nbsp; &nbsp;=&nbsp;10Category 2 count&nbsp;within&nbsp;filter&nbsp; &nbsp;=&nbsp;5Category 1 count&nbsp;outside filter&nbsp;=&nbsp;0&nbsp;Category 2 count outside filter =&nbsp;0Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = 15&nbsp;&nbsp;Apply some filter using slicer&nbsp;Category 1 count within filter&nbsp; &nbsp;=&nbsp;7Category 2 count&nbsp;within filter&nbsp; &nbsp;=&nbsp;2Category 1 count outside filter = 3 (10 - 7)Category 2 count outside filter = 3 (5 - 2)Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = 15&nbsp;Thanks

v-yulgu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
You may need some measures to achieve such a result. For example, measures could be similar to:
Category 1 count within filter =
CALCULATE (
    SUM ( Table[Amount] ),
    FILTER ( ALLSELECTED ( Table ), Table[Category] = "Category1" )
)Category&nbsp;1&nbsp;count&nbsp;outside&nbsp;filter&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Table[Amount]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;ALL&nbsp;(&nbsp;Table&nbsp;),&nbsp;Table[Category]&nbsp;=&nbsp;"Category1"&nbsp;))&nbsp;&nbsp;&nbsp;&nbsp;-&nbsp;[Category&nbsp;1&nbsp;count&nbsp;within&nbsp;filter]
&nbsp;
For more advice, please provide sample data as suggested in this blog.
&nbsp;
Best regards,
Yuliana Gu

anonymous · Answer

Amazing, thats part of the solution,&nbsp;it is giving me the&nbsp;correct counts now. The only other problems are on how to display in the way I mentioned above along with totals. Sorry&nbsp;if this sounds a silly question.&nbsp;Good that the counts are calculated and showing correct values. I want them to be displayed in rows along with some static values, please see attached image on what i actually need.The&nbsp;Measures is what i can calculate now, what about the other bits and formation of table&nbsp;with totals and dynamic data?&nbsp;Thanks for the response so far, much appreciated.&nbsp;&nbsp;&nbsp;

Forum Discussion

Custom table with dynamic data

6 Replies

Related content

Fabric Link - Dynamics Customer Insights

Custom partitions of tables

Dynamic Table Creation???

Table with dynamic grouping of rows

Dynamic filtering between two tables