Help with Filter function

[gcanelon]

Hello, we are trying to provide companies particiating in public tenders a list of what competitors they should keep an eye on. For this we are using a public dataset that contains information on tenders that have been conducted throughout the years, similar to the table shown below.

What we want to do is select a provider in a slicer and see what competitors beat the selected provider more frequently in tenders they participated in (and if possible see how frequently their competitors beat them even having a higher price). 

We posted this same problem before but we were provider with a solution where the name of the provider has to be hardcoded in the formula (see below). We need to be able to see the information for any provider we select in a slicer. 

This is the formula that was offered as a solution, notice that the provider's name has to be entered in the formula. We would like the formula to reference the name of the provider selected in a slicer. 

newTable =
VAR _tt =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), [Provider] = "Provider1" ),
        'Table'[Request Item]
    )
RETURN
    FILTER ( 'Table', [Request Item] IN _tt && [Status] = "Adjudicated" )

This is a simplified version of our source table. Thanks in advance for your help. 

[parry2k]

@gcanelon your end goal is not clear, you cannot use slicer value in the calculated table and it is not going to work. Few things, why you need a calculated table, 2nd, simply put a sample pbix file with the expected output, it will help everyone to provide an effective solution. Just sharing something not working, and not explaining what is the expected output, makes things a bit harder to understand.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[v-jingzhang]

Hi @gcanelon 

I have another idea about this requirement. You could add a new table containing all the tenders and their winner providers' info like below. I changed column names by adding 'Winner' in the new table to distinguish them from columns in original table. And create a relationship between tables based on Requested Item columns.

Table_Winners = FILTER('Table','Table'[Status]="Adjudicated")

Then add a slicer with the field 'Table'[Provider]. Select the columns you need from two tables into a table visual. You will get the result you want. You will see the winners' price and compare the prices easily. If you want to highlight the higher or lower prices, you could use conditional formatting.

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[gcanelon]

Hello v-jingzhang, that is the solution that we have been using but it has the inconvenience that increases the size of the dataset. We are looking for something that will not have that consequence. For anybody without that concern it's perfect. Thanks for your reply. 

[AllisonKennedy]

@gcanelon  , I agree with @amitchandak  that using SELECTEDVALUE will help avoid having to hard code the provider in your DAX, however this must be done as a new MEASURE (not a table).

Power BI Order of Operations 

If no provider is selected or more than one provider is selected, it will return an empty table.  If you use his second suggestion using VALUES() it will work when any number of providers are selected. 

[amitchandak]

@gcanelon , expected output is not clear. Can you share that in a table format.

You need to create an independent table for provided and then have slicer on that and try formula like 

newTable =
VAR _tt =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), [Provider] = selectedvalue(Provider[Provider] ) ),
        'Table'[Request Item]
    )
RETURN
    FILTER ( 'Table', [Request Item] IN _tt && [Status] = "Adjudicated" )

or

newTable =
VAR _tt =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), [Provider] in values(Provider[Provider] ) ),
        'Table'[Request Item]
    )
RETURN
    FILTER ( 'Table', [Request Item] IN _tt && [Status] = "Adjudicated" )

[gcanelon]

Hello Amit. We tried your approach but there must be something we are doing wrong. The forula we are using is this: 

newTable = 
VAR _tt =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), [Provider] = selectedvalue(Provider[Provider] ) ),
        'Table'[Request Item]
    )
RETURN
    FILTER ( 'Table', [Request Item] IN _tt && [Status] = "Adjudicated" )

But the behavior is as you can see in this video. Any suggestions? 

[AllisonKennedy]

@gcanelon A table will never update based on slicer selection due to Power BI Order of Operations 

Please try doing this as a measure, not a table, then add it as a filter to your visual. 

If you are wanting to show providers that are not in the selected slicer, you will either need to create a new table of providers that is NOT RELATED to your fact table, or clear filters and reapply them as you want using a measure. 

This video uses the duplicated Dim table method, but you might be able to get your desired result with just a measure. 

https://youtu.be/n3Hp-eNqudw 

Can you please share the sample pbix via OneDrive or other link?