Trying to use a slicer to filter an un-related table

[Roseventura]

I know this is a very simple thing but after 2 days of trying everything to no avail, I'm not sure what I'm doing wrong.

I have an AWARD table that contains a part number, Award Qty and Award Status (which is a measure - see below).

Award Status =

IF([Total Award Qty]= 0, "No Award",

IF([POS Qty During Award Period] = 0,"RED ALERT!",

IF([% of Award Complete to POS Qty] < [% of Award Complete to POS Days],"Under-performing","On Track")))

I created an un-related table 'AWARD DISPOSITION', which stores the Award Status values:

Out of many other measures I tried, I thought this one would work, but it doesn't:

Award Disposition =

VAR SelectedDisposition = selectedvalue('Award Disposition'[Award Disposition])

return

   CALCULATE([Total Award Qty],

       filter(PBI_Awards,[Award Status] = SelectedDisposition ))

The above Award Disposition measure returns blank. All I want is to have the slicer to filter my table based on the selected value. If I select 'On Track' from the slicer, then I need only those to display in my table.

Here's what it returns:

[mahoneypat]

Please try this expression instead

Award Disposition =
VAR SelectedDisposition =
    SELECTEDVALUE ( 'Award Disposition'[Award Disposition] )
RETURN
    CALCULATE (
        [Total Award Qty],
        FILTER ( VALUES ( PBI_Awards[Part Number] ), [Award Status] = SelectedDisposition )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Roseventura]

Mahoneypat,

I tried your measure and unfortunately it did not work.  I still got a blank column as a result.

The other thing that occurred to me when I was looking at both your measure and mine is that I'm NOT trying to return a sum of the Award Qty with this measure.  I want to return the Award Disposition that MATCHES the Award Status.  Here's what I mean (in Excel):

If I can achieve that, then I can remove the Award Status column and use Award Disposition to filter.

Rose

[mahoneypat · Accepted solution]

I was able to reproduce your scenario and believe this approach will work.  Please try the following

Make a new measure like this one using your slicer table[column] instead

Matches Slicer =

IF([Award Status] in VALUES(SlicerChoices[Award Status]), 1, 0)

Then add the [Matches Slicer] measure as a Filter on your table visual with "is" 1.

I used VALUES so that it would show when no selections or multiple selections were made in your slicer (instead of SELECTEDVALUE).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Roseventura]

Mahoneypat,

Thank you so much!  That did the trick!

Rose

[FrankAT]

Hi @Roseventura,

I think you have to connect the Award Disposition table (see figure). Create the slicer from Award Status. It filters your Awards as expected.

 Regards FrankAT

[Roseventura]

Hi,

Thanks for your response, but I cannot join Award Dispostition table with the Awards table because the Award Status is a measure.

Rose