Filter a table visual based on a slicer selection

[Aklys]

Hi,

I have a requested functionality that I'm not sure Power BI is currently capable of but thought I'd check here if anyone has a solution I may not have considered on how to address this.

Scenario

I'll overly simplify this description to the key parts. I have a fact table (Items) and a dimension table (Parent). The fact table is made up of multiple Item Types that have been merged, for this example there is an item type called Status Update and another that is Key Date. These items are grouped by the parent table rows but that's not overly relevant other than it reduces the dataspace of the items.

I have a slicer for the Date attribute related to a Status Update Item that the user of the report page utilises. When the slicer value is selected it will be utilised in a measure identifying the minimum date that is greater than the selected date for a Key Date item (the date fields for Status Update and Key Date items are different fields but unsure that is important in this case). This value generates successfully and is validated by displaying it in a card and confirming the outputted value.

The value generated for this measure is then desired to be utilised to filter a table visual to only show Key Date items that are before or equal to this calculated date.

Problem:

How do I take the known working value that is displayed in a card for the measure and utilise it to filter down the Key Date items displayed in the Table View? Is there even a possibility that this can be done in Power BI?

[TomMartens · Accepted solution]

Hey @Aklys ,

I'm. not sure if I understand your question correctly, but I will give it a try ...

I have a very simple table like the one below:

Now I want to filter for the rows that have a SalesAmount larger than or equal to 100M. For this I define a "check measure". This measure returns 1 if a condition is met, BLANK if not. The below shows the DAX of this check measure:

SalesAmount (more than 100M) = 
IF( [SalesAmount (ms)] >= 100000000, 
    1,
    BLANK()
)

Then I this measure on the filter pane for the visual:

When I hit "Apply filter", the table visual only shows the expected rows.

I use this technique quite often, of course you have to make sure that performance of this measure is good 😉 Sometimes it requires some more thinking because the "check-measure" is utilizing the filter context of the row. But I use this because the check-measure or the base measure (here it is SalesAmount (ms) ) do not have to be part of the data in the table visual.

Hopefully, this gives you an idea of how to tackle your challenge.

Regards,

Tom

[TomMartens · Accepted solution]

Hey @Aklys ,

I'm. not sure if I understand your question correctly, but I will give it a try ...

I have a very simple table like the one below:

Now I want to filter for the rows that have a SalesAmount larger than or equal to 100M. For this I define a "check measure". This measure returns 1 if a condition is met, BLANK if not. The below shows the DAX of this check measure:

SalesAmount (more than 100M) = 
IF( [SalesAmount (ms)] >= 100000000, 
    1,
    BLANK()
)

Then I this measure on the filter pane for the visual:

When I hit "Apply filter", the table visual only shows the expected rows.

I use this technique quite often, of course you have to make sure that performance of this measure is good 😉 Sometimes it requires some more thinking because the "check-measure" is utilizing the filter context of the row. But I use this because the check-measure or the base measure (here it is SalesAmount (ms) ) do not have to be part of the data in the table visual.

Hopefully, this gives you an idea of how to tackle your challenge.

Regards,

Tom

[Aklys]

Not sure if this approach is applicable or not but be curious if it is. This is repost I've made of the issue with a demo as suggested by others, if it helps understand the situation better.

https://community.fabric.microsoft.com/t5/Desktop/Filtering-a-Table-Visual-based-on-a-value-calculat...

[v-rzhou-msft · Accepted solution]

Hi @Aklys ,

According to the post link, it seems that your issue has been resolved by our engineer.

Please mark the helpful reply if your issue has been resolved. Then more people who have similar issues like yours could see it.

Best Regards,
Rico Zhou

[danextian]

Hi @Aklys 

That is not possble. Calculated tables or any other physical tables are stored in-memory and update only upon a refresh or the underlying data or the formula is modified. Whilen you can use a measure to create a calculated table, it is as if the measure isn't filtered at all. Table view affects the report view but not the other way around.

[Aklys]

Yeah I had thought as much. I was hoping there was some other method someone may suggest I hadn't realised was available to utilise the user's slicer selection. Like a measure creating a new ephemeral table or something that I can display in the table visual. But it seems not by your explanation. Thanks for your response.

[danextian]

I was there ages ago.

I would create a separate post and not oversimplify my requirements, provide workable sample data (not an image) and the expected result from that.

[Aklys]

I usually do that, but in this situation it's a lot of work for likely the same result of what you just stated, hence the simplification. If no one knows of a method to utilise a user selection from a data populated choice, to be used in a creating a calculated value that can be used to filter a visual, then the work to give a demonstration is a moot point. lol

[danextian]

There may be workarounds like using a disconnected table and a measure but without a sample data we can only shoot a target without properly aiming at it.

[Aklys]

Here is the new post with the demonstration of data and desired outcome attempted to be explained. But I'm still not entirely sure it's possible.

https://community.fabric.microsoft.com/t5/Desktop/Filtering-a-Table-Visual-based-on-a-value-calculat...

[Rupak_bi]

Hi @Aklys ,

Still I suggest to share sample data and desired output. there may be some possibility in otherway. sample data helps to understand the aks in a better way and so is the solution. will try