How to show all values based on multiple slicers?

[sylvioros]

Hello folks,  I've just started to work with Power Bi and I need your help to figure it out this issue.

I've build a dashboard to help me visualize studies published about a a certain topic.  I want to use research areas to count citations and number of articles published, so I split the column "Research Areas" in 3 and I created 3 slicers (since each study can be categorized in up to 3 areas).

My goal is to select a field in all three sliders (e.g. "Business & Economics) and see all articles published, % of citations and % of papers published

It works fine if I selected different values( e.g.: "Business & Economics" + "Computer Science"), the problem is: when I want to selected the same area in others slicers to get all citations from the field.

I know why this is happening since a paper can't be in the same field twice, but I don't know to solve this in Power Bi. I tried to edit interactions, but it didn't help.

I appreciate any help!

p.s.: to create 3 columns was the only way I could think to solve. If there's other way, please, tell me. Remember: originally, all research areas are in the same column, separated by semicolon.

[Wilson_]

Hello sylvioros,

The first thing I would do is change the table in Power Query so all the research areas are in one column, but separated. I've created a super simple dummy dataset to illustrate how I did it, if you edit the query on the table to check. Essentially, it involves three steps:

• Splitting the original column by the "; " delimiter
• Unpivoting all the new research area columns
• Removing the new column that used to just be a header name

What this does however is duplicate the studies so you will need an additional step in your measure when you calculate percentages to make sure studies aren't duplicated (assuming that's something you want).

Can you please share how you're calculating % of citations and % of papers published?

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[sylvioros]

Hi Wilson, thanks for replying!

I had thought about unpivoting solution, but I didn't think it would in fact work. 

What exactly should I do after unpivoting the columns ? p.s.: there are now upt to 4 research areas, which means each article are in 4 rows.

These are my measures at the moment:

Cit % = DIVIDE([Cit_total]; CALCULATE(SUM('savedrecs (2)'[Citations]); ALL('savedrecs (2)'[Research Area 1]);ALL('savedrecs (2)'[Research Area 2]);ALL('savedrecs (2)'[Research Area 3]) ))

Qtd Papers % = DIVIDE([Qtd papers (sum)]; CALCULATE(COUNT('savedrecs (2)'[Article]); ALL('savedrecs (2)'[Research Area 1]);ALL('savedrecs (2)'[Research Area 2]);ALL('savedrecs (2)'[Research Area 3]) ))

[Wilson_]

Hello slyvioros,

You are correct, but you can correct for that by essentially reaggregating unique articles in your measures by calculating over a table built with the SUMMARIZE function.

I should've asked for this too earlier; can you link your pbix or at least a smaller/sanitized version of it? I can't quite tell what's going on just from the measures alone, especially since they reference other measures it seems, hah. 😄

[sylvioros]

Hi Wilson, 

I'm not quite familiar with SUMMARIZE function yet.

I saved a smaller version of pbix and data. Please, download here. 

In the file, the columns weren't unpivoted, so you can see the dashboard working.

More kudos to you mate.

[Wilson_ · Accepted solution]

Hi sylvioros,

I've re-linked an updated version of your sample pbix here.

I added a second data source with the Excel file you provided with the transformations I would've made. I've also replicated your measures into the new table with how I would calculate it. There are comments included with my reasoning and explanations in the measures as well.

Please note that the percentages by research area will sum to greater than 100% because papers often have multiple research areas; an agriculture and anthropology paper would count toward both agriculture and anthropology.

Hopefully the different methodology and DAX functions makes sense to you and is something you can use in other projects as well. Please let me know if that's what you were looking for or if you needed something different. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[sylvioros]

Hello Wilson, what a heck of a job mate. Thank you very much!!

I spent some time with your work, and I took the opportunity to study the SUMMARIZE function and the logic behind the measures, which were great! Thank you again.

When I updated your file, things were great, but when I tried to replicate what you did on my original file, I faced a strange issue.

When I select a research area, it shows a smaller number.

Issue 2: only with my pbix:

I don't intend to take more of your time as you're already very helpful, but what do you think may be going on?

[Wilson_]

You're welcome, sylvioros. 😄

That is strange and quite the difference. I double checked the one I'd uploaded to see if the same issue existed there, but it didn't seem to. Does it happen consistently to all the research areas? I'm not thinking of any reasons why that would be the case off the top of my head. Unfortunately, I think I'd have to look at the full pbix to figure it out.

[sylvioros]

Apparently it does happen accross all areas. Well, it could be something going with my file, since with yours works smoothly.

Thanks Wilson. Cheers1

[Wilson_]

Too bad we couldn't quite get it to work in your full pbix file. 😞

I would be happy to take a look at that too if you're comfortable with that.