Is possible to filter this way?

[AndresSalomon]

Hi lovely Community. Me again with problems. Maybe more like a doubt, because the other day I was asked if this can be done and my brain just stopped working. 

I have this Pivot Table (or a Matrix), based on a raw data with similar columns. The Amount column is just a DAX function like SUM(...). 

Name ID__________Country___________Boss Name___________Project ID____________Amount
Name1___________Argentina____________Batman________________14__________________100
________________________________________Superman_______________21__________________53
Name1 Total_________________________________________________________________________153
Name2____________Hungary____________Batman_________________15__________________88
______________________________________WonderWoman___________65__________________109
Name2 Total_________________________________________________________________________197
Name3_____________Russia_____________Aquaman_______________54__________________67
________________________________________Superman_______________24__________________320
Name3 Total_________________________________________________________________________387
Name4______________USA_______________Flash___________________29__________________90
________________________________________Batman__________________18__________________101
Name4 Total_________________________________________________________________________191
Name5______________Korea__________WonderWoman____________71__________________14
________________________________________Aquaman________________97__________________12
Name5 Total_________________________________________________________________________26

So the question I was asked was something like: Hey, is possible to filter only those Name IDs associated with Batman but also leaving there the other Bosses Names?

For example, following the logic of the question, if this is possible, after the "Batman Magic Filter" I should obtain a Pivot Table (Matrix) like this:

Name ID__________Country___________Boss Name___________Project ID____________Amount
Name1___________Argentina____________Batman________________14__________________100
________________________________________Superman_______________21__________________53
Name1 Total________________________________________________________________________153
Name2____________Hungary____________Batman________________15__________________88
______________________________________WonderWoman___________65__________________109
Name2 Total________________________________________________________________________197
Name4______________USA_______________Flash___________________29__________________90
________________________________________Batman_________________18__________________101
Name4 Total________________________________________________________________________191

And the same working if I choose another super hero (please feel free to add Marvel's characters here).

Do you think this is possible? I know that with the funcionality of the Slicers, if I add the "Boss Name" column it will filter ONLY those lines where I have Batman, for example, but wouldn't be showing the real total amount of the Name ID. And imagine that I'm managing the finance of all DC's characters, so I can't go one by one filtering all the possible combinations...

I don't know if this could be done with DAX or some other functionality that I don't know. Any advice here would be helpful.

Well, please let me know if you don't understand or if you need more information. 

Thanks in advance! 

Kind regards,

Andy.- 

[Ashish_Mathur · Accepted solution]

Hi @AndresSalomon,

You may refer to my solution here.  This solution overcomes the limitation mentioned in my previous message.

Hope this helps.

[AndresSalomon]

Hello @Ashish_Mathur, thank you very much for your help and tenacity with this. Such a big deal, isn't it?

I was trying the solution you provided and it works, I think, in a small scale. I mean, we have only a few combinations of super heroes there, but what would happen, as my real case, if we have thousands and thousands of records in the raw data with a lot of possible combinations. 

Wouldn't it be very confusing? Because you will have to go one by one searching for that super heroe you want in all the possible combinations. I think it will be exhausting.

I'm convinced that it should be another solution for this. I can't accept that this is not possible, considering the power of DAX and Power Query. 

Thanks a lot again. I will keep trying with both workarounds here and will let this post open so maybe comes another solution. 

Kind regards,

Andy.-

[Ashish_Mathur]

You are welcone.  What do you mean by "go one by one searching for that superhero you want in all possible combinations"  one you drag the Cobined superheroes field to the Report select, you just have to type Batman in the Report filter search box and all instances where Batman is availbale, will appear in your Pivot Table.

[AndresSalomon]

Hello @Ashish_Mathur, you are absolutely right. I think I underestimated your solution, and I'm sorry about that.

Another question, do you think this workaround can be applied in Power BI? Because there you only have slicers, and in that case yes it will be very messy if you work with lots of combinations in that Custom Column.

On the other hand, don't you think that it should exist another solution to this? I mean something more intuitive that maybe we are missing here. As your solution, a Custom Column is the key to filter this way, but I'm just so curious if there isn't a DAX formula that can help here. Maybe I'm being greedy, I don't know.

Anyway, thanks again!! So appreciated. 

Kind regards,

Andy.-

[Ashish_Mathur]

Hi,

Drag the All resources field to the Page filter and using Advanced filters there, select contains.  See image below.  I do not know of a DAX only solution - if i did, i would have shared it.  Also, look at it this way - you have come a long way from having absolutely no solution to a working solution.

[AndresSalomon]

Thank you very much @Ashish_Mathur! And you are right, a working solution is always welcome 

Thanks again for your advices and your knowledge!!

Kind regards,

Andy.- 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

You may refer to my solution here.  I have used a combination of Power Query and PowerPivot to solve this problem.  In the Merged report filter, i have chosen Batman.  Please note that this is not a perfect solution - there is one piece here which i still have to improve upon.  In the meantime, this should get you started.

Hope this helps.

[kcantor]

@AndresSalomon

A disconnected slicer reference in a measure should work for this. Create a table of just hero names to reference in your calculation. Then create the sum measure referencing that slicer table. You might have to display the two measure side by side but you would only have to create one calculation.  Just like the Flash you would be finished and moving on. 

https://www.fourmoo.com/2017/08/08/power-bi-how-disconnected-slicers-can-enrich-transform-your-analy...

[AndresSalomon]

Hi @kcantor, thank you very much for replying! 

What you proposed is like the Parameter Table that Marco & Alberto posted in their blog (CREDITS FOR THEM), right?

http://www.daxpatterns.com/parameter-table/

I think that this is not too far for the solution that I need, but my limited knowledge of DAX is not playing at my favor. 

As you said, first I have to create a disconnected table (I mean withouth relationships right?) with the unique values of the heroes:

"HeroTable"
Hero

Batman

Flash

Aquaman 

else...

But I'm having troubles with the measure. I'm not fully understanding how to build it in order it can filter (using the "HeroTable" as a slicer) the way I need. 

I have the Amount as a measure like: [Amount]=SUM (FactTable[AmountColumn])

Now with that, I should go and create the measure explicit in that blog, right? Something like:

[AmountNewMeasure]=

VAR HeroSelected = SELECTEDVALUE (HeroTable[Hero], "No Hero Selected")

RETURN

IF (HeroSelected = "No Hero Selected", [Amount], 
CALCULATE( [Amount], FactTable[Hero Column] = HeroSelected)
)

Is right to use the FactTable there (in bold)? I have the doubt if this is going to filter the Name ID associated with all the Heroes (explicit in the post), and is not going to filter only the lines where is Batman, for example. 

Any comment here would be helpful. Again thank you! 

Kind regards,

Andy.-

[kcantor]

@AndresSalomon

That is correct except you may need additional look up tables. Here is what I did and what I received back from PowerBI. Shoot me a message and I will send you the very small file.

[AndresSalomon]

FactTableNameTablePanelRElations
Hi @kcantor, thanks for this!

I think we are close to the solution but I'm still with some concerns. 

First, I think that showing the 2 measures togheter is confusing for someone that reads this for the first time, apart from you or me.

And second, following the prior condition and leaving only the "Selected Hero Amount" measure, when I filter by Batman it leaves me only the rows related to Batman, leaving me with the same problem I had at the beggining. 

Sorry about being so insistent with this, but I have to present this to my superior so I think you would understand the importance of the details here.

Well, please let me know if you think in a workaround for this. I will keep trying also. Thanks again! 😄 

Kind regards,

Andy.-