cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

filter on dynamic columns based on slicer

Hi,

in a table, I have three columns named col1;col2;col3, each one contains eather a 1 or a 0. I have also another column for the sales.

In another table, I have a column that contains col1;col2;col3 (I have created manually, but I think I can created automatically using VALUES function).

 

In my dashboard, I would like that the user has a slicer where he can select any of the three col1;col2;col3 (either only 1 but multiple selection should be allowed as well), and depending on his selection the proportion of the sales is computed when colX =1

i don't know how to refer to these columns to indicate tablename[col selected in the slider]=1

 

Also I have other filters so I would like to keep these filters. I have attached an example of what I would like.

Thanks,

 

PS: is this the optimal way the database should look like? another solution would be to put into long format?

Since I am new in Power BI I can't figure out how to structure my data in an optimal way for power BIpowerbi example.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: filter on dynamic columns based on slicer

Hi @Anonymous,

 

I'm a little confused about your requiremet now. Do you want to get the output like below?

 

output.PNG

 

If it is, you don't need use selectvalue function the get the value you select in slicer, you could create the formula below.

 

sum = CALCULATE(SUM(Table1[Sales]),FILTER('Table1','Table1'[Value]=1))

If you still need help, could you show your desired output so that we could help further on it?

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: filter on dynamic columns based on slicer

Hi @Anonymous,

 

Do you want to get the output like below?

 

column sum.PNG

 

If that is your desired output, please follow the steps and you don't need to enter another table with a column contains col1, col2, col3.

 

1. Unpivot columns with Column1, Column2, Column3 in Query Editor and Close and Apply.

 

unpivot.PNG

2.Then you could create the measure under Modeling tab with the formula below.

sum =
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        'Table1',
        'Table1'[Col] = SELECTEDVALUE ( Table1[Col] )
            && 'Table1'[Value] = 1
    )
)

3. Then you will get the output.

 

More details, you could refer to the attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: filter on dynamic columns based on slicer

Thanks! I really want to give a trial to your solution before telling you if it matches my case or not but could not open your file.

Which power bi desktop version do you have?

I was running the 2.54 but could not open any of your file, my company has the 2.60 that I will download.

 

Thanks,

 

Anonymous
Not applicable

Re: filter on dynamic columns based on slicer

Hi again @v-piga-msft

 

Thanks for your answer now that I got a newer version (2.60) I could make your example work and I am glad to realise that I follow the logic you propose. 

In theory it is working if the user selects a unique column but in practice there will be 20 or more choices and user should be able to select 1 or 10! (or any other number). 

But since isselected work for only a single choice, I am stuck.

 

How would you do that?

I was thinking from a database viewpoint of each time a user selects one thing, a "table" with the corresponding products and having condition=1 is computed and after whatever the number of tables, an union of these tables should be done to get all the relevant products and compute a sum.

In theory this should work? But I might not have the right database logic to solve it in power bi ? (and I have checked calculated table does not able to do what i want).

 

Thanks for your time, I really think power bi is the right tool and what I request is typical things straight forward for power bi (slicers....) but it takes time to program what we want!

 

 

Anonymous
Not applicable

Re: filter on dynamic columns based on slicer

Any insights on how to solve my issue?

Thanks for your help,

 

Regards,

Community Support Team
Community Support Team

Re: filter on dynamic columns based on slicer

Hi @Anonymous,

 

I'm a little confused about your requiremet now. Do you want to get the output like below?

 

output.PNG

 

If it is, you don't need use selectvalue function the get the value you select in slicer, you could create the formula below.

 

sum = CALCULATE(SUM(Table1[Sales]),FILTER('Table1','Table1'[Value]=1))

If you still need help, could you show your desired output so that we could help further on it?

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: filter on dynamic columns based on slicer

Thanks it indeed work,

What I was not clear is that I need to put my data in long format to make what I want.

Although I will need to figure out some tricks not to count twice (because now the data are repeated so count of sales is not anymore exact!

I will try to figure out by myself and ask again some questions if I am stucked with other questions,

 

Thanks again for your help!

Regards,

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 295 members 3,378 guests
Please welcome our newest community members: