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

DAX DAX Calculations
1

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).

Capture 38

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:

Capture 39

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:

Capture 40
Capture 40659×783 72.4 KB

I’ve created many of these un-related tables and used as filters before, but they always referred to a measure which calculates something, like TOTAL SALES. In this case, I just want it to filter based on the results of a measure which results in a TEXT field (On Track, Under-performing, RED ALERT!!, and No Award).

Rose

2

Hi @Rose,

I have tried replicating your scenario based on the information. However ,a sample pbix file from your end will be helpful.

Also you can see this blog

See if the attached .pbix helps.

Awards.pbix (40.8 KB)

3

Hi @Rose:

I just have done a post on very relevant thing that you are looking for.

Here goes the post link:
https://forum.enterprisedna.co/t/sales-ecosystem-share-collaborate-learn-connect/7495/4?u=sheik_mishuk

I believe, if you use the combination of “IF” and “Selected Value”, your problem will be solved.

Would be more helpful, if you share a PBIX file.

Regards

4

Thank you for your response.

However, I’m not trying to change the visual based on several measures. I’m trying to filter the table based on only ONE measure. Your example uses SWITCH to switch between several different measures. I only have the ONE measure - AWARD STATUS.

I actually tried this, but realized that I’m not trying to switch between different measures, I’m only trying to filter based on what the AWARD STATUS is. Keep in mind that AWARD STATUS is a measure. It does not exist in a table so there is no relationship between my Award data table and the Award Disposition table.

Rose

5

Hi @Rose,

Is it possible that you can upload your file in order we can look at the file? This would help us try to investigate the issue.

thanks
Keith

6

Keith,

I tried to upload my PBIX file, but it’s too big. I tried to compress it and even create a smaller version of it, but the file is still to large to upload.

The good news is that I was able to get a solution from another forum. This is the solution (kudos to mahoneypat):

I created a new measure using the slicer table:

 Matches Slicer = 
       IF([Award Status] in VALUES('Award Disposition'[Award Disposition]), 1, 0)

Then I added the [Matches Slicer] measure as a filter to my table where [Matches Slicer] is 1.

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

Here is the result when “Under-performing” is selected from the slicer:

Capture 42
Capture 421475×735 209 KB

1 Like
7

Hi @Rose,

I’m glad you found a solution and posted it in the forum.

Thanks
Keith

1 Like