Filter with measurement

[matemusic]

Hi Everyone, 

I hope you will be aple to help with the following challenge.

In data model I have a table with next columns: date, product, sales and goal. In table there are 5 products.

For selected period in report I want to count products, which had bigger sales comparing to goal and products, which had smaller sales comparing to goal.

So I created two measures:

Count of failed products = 
var tab = SUMMARIZE(Table1;Table1[Product];"Sales vs Goal";sum(Table1[Sales])-sum(Table1[Goal]))
var coun = COUNTAX(filter(tab;[Sales vs Goal]<0);Table1[Product])
return coun

Count of succeded products = var tab = SUMMARIZE(Table1;Table1[Product];"Sales vs Goal";sum(Table1[Sales])-sum(Table1[Goal]))
var coun = COUNTAX(filter(tab;[Sales vs Goal]>0);Table1[Product])
return coun

It worked fine:

 

Even if I filter by date:

But, then I want to filter back: If i clik on failed products, i want to tho sales and goal just for failed products.

I understand, this cant work. But maybe there is a way? I could do it with with dummy table, but my original data model is too huge, beacuese it takes to long to calculate.

My two measures knows, which products are failed or succeded, but how can I this list of products bring to barchart?

This problem has charged me quite a while, so I'll be very grateful for your help.
All the best, matemusic

[Anonymous]

Hi @matemusic,

I build a sample table with your measure, but I can't click on pie chart, can you share me some detail info about this or provide a sample file?

In my sample, the product all displayed as failed.

Regards,

Xiaoxin Sheng

[matemusic]

Hey, I will create demo file with working solution and share it with you 😉

[Anonymous]

I feel as if this *is* possible, but you are going to have to use a "disconnected slicer" (aka, "disconnected table") and some tricky DAX.  I would look through some P3 archives and see if you are inspired 🙂   http://www.powerpivotpro.com/tag/disconnected-slicers/

Like, a table that JUST has 2 rows "Failed" and "Succeeded".  Then measures like...

MySuperMeasure = IF ( [Current Success or Failure Table Value] = "Failed", [FailureMeasure], [SuccessMeasure])

Use MySuperMeasure AND the field from your 2 row rable in your visuals?

[matemusic]

Hey, thanks for your help, but it didnt solve my problem. Anyway, with today new Power bi release, I can finally get excepted result .

I used new Top N filter feature and one easy trick 🙂   

I will try to create demo pbix file and share it.