Filter Data Removing Rows with Same Key

[Luke-Howells]

Hi,

I am building a dashboard but have an issue with filtering data. I have a main data table which contains a column with a master key  (Quote ID) and a licence type. I then have another table which contains all available licence types that is used in a filter. The idea is you set a filter and any of the Quote ID's that don't match get filtered out of the data table. The problem is within the data table there are more than one occurance for some of the Quote ID's. If a quote ID has an accepted licence type and a non accepted licence type then it still remains in the data table. How do I get it so the quote ID is removed from the data table even though it has an accepted licence type but fails on the one that is not? Some screen shots below to help understand. Sorry if not explained very well. All help is very appreciated. 

EXAMPLE

If I applied the filter to accept on licence type F then the Quote ID should disappear from the table on the bottom right. 

At the moment if I do this then the quote ID remains because the other licence type is not being filtered out. 

[Luke-Howells]

Would be ideal if it could be a column as apposed to a measure. I have a third table which my driver table links too which contains unique Quote ID's and is used to show the number of quotes. I need to show the results in this final table if possible. 

[johnt75]

Try creating a measure like

Licence Type Filter =
VAR thisQuoteLicenceTypes =
    CALCULATETABLE (
        VALUES ( 'Quote'[Licence Type] ),
        ALLEXCEPT ( 'Quote', 'Quote'[Quote ID] )
    )
RETURN
    IF (
        ISEMPTY (
            INTERSECT ( thisQuoteLicenceTypes, VALUES ( 'Licence Types'[Licence Type] ) )
        ),
        1
    )

and use that as a filter on your table visual to only show when the value is 1

[Luke-Howells]

Hi John, Thanks for this. It's kinds of what I want but when I used this the results show a 1 for quotes that don't have that licence type. (if that makes sense). I want to show quotes that have the selected licence type unless there is more than 1 licence type for the quote and one of those licence types haven't been selected. 

[johnt75]

OK, I think the below will work

Licence Type Filter =
VAR thisQuoteLicenceTypes =
    CALCULATETABLE (
        VALUES ( 'Quote'[Licence Type] ),
        ALLEXCEPT ( 'Quote', 'Quote'[Quote ID] )
    )
RETURN
    IF (
        COUNTROWS(INTERSECT ( thisQuoteLicenceTypes, VALUES ( 'Licence Types'[Licence Type] ) ) ) = COUNTROWS( thisQuoteLicenceTypes),
        1
    )

[Luke-Howells]

Fab thanks John! It works great in a table but I want to pull through those results into another table. Would there be anyway where it could be done as a column instead of a measure?

My results com through like the below but I can't do much with it in a table measure. I tried to use the measure in a filter but can't get that to work 😞

[johnt75]

You can use the measure inside a FILTER function, so you could do something like

Sample Measure = SUMX( FILTER( 'Quotes', [Licence Type Filter] = 1), 'Quotes'[Price])

[Luke-Howells]

I've tired putting a measure in and I keep getting a result of 75 billion. I don't even have that many rows lol. I must be going wrong somewhere. 

SUMX(FILTER('01b. Data [Vehicle]',[Licence Type Filter] = 1), '01b. Data [Vehicle]'[QuoteID])

Licence Type Filter = 
VAR thisQuoteLicenceTypes =
    CALCULATETABLE (
        VALUES ( '01c. Data [Driver]'[LicenceType] ),
        ALLEXCEPT ( '01c. Data [Driver]', '01c. Data [Driver]'[QuoteID] )
    )
RETURN
    IF (
        COUNTROWS(INTERSECT ( thisQuoteLicenceTypes, VALUES ( '30. Licence Type'[LicenceType] ) ) ) = COUNTROWS( thisQuoteLicenceTypes),
        1,0
    )

[johnt75]

You're summing up the quote IDs which is why you're getting such a ridiculously large number. If you want the number of quotes then you could try

COUNTROWS( FILTER('01b. Data [Vehicle]',[Licence Type Filter] = 1) )

[NickolajJessen]

This looks pretty smooth.

Can you tell me what the whole calculateTable and Allexcept is for?

What would be the problem of doing just:

LicenceT Type Filter = 

Var T1 = values(Data[LicenceType])

Return
IF( 
    ISEMPTY(
        INTERSECT(T1, VALUES(Licencetypes[LicenceType]))
    ),
    1
)

[johnt75]

Data[Licence Type] is being filtered, so the ALLEXCEPT makes sure that all values of Licence Type are retrieved

[PaulDBrown]

Can you please share a non-confidential data sample?

[Luke-Howells]

Hi Paul, please see below. 

Data Table

Quote ID	Licence Type
1234	A
1234	B
1235	C
1236	A
1237	A
1237	C
1238	A
1239	A
1239	B

Filter Table

Licence Type

A

B

C