cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ignas Member
Member

Need help with filtering

Hey there,

 

It is a simple example of what I need to achieve:
1.PNG

 

If I select the answer "ddddd" then  the table should show all the answers from the "recipientID" answers, not only the answer that was selected in the filter.

I cannot filter by RecipientID in the filter in the real world situation.

Does anybody know how I can do this using magic of dax?

Here is the file:

PowerBI_report_Example

4 REPLIES 4
Super User III
Super User III

Re: Need help with filtering

Hi @ignas ,

 

Follow the steps below:

  • Create a table for the answers slicer:
    • Answers = DISTINCT(Survey[Answer])
  • Create the following measure:

 

Filter answer =
VAR USERID =
    LOOKUPVALUE ( Survey[RecipientID]; Survey[Answer]; VALUES ( Answers[Answer] ) )
RETURN
    IF (
        DISTINCTCOUNT ( Answers[Answer] )
            = CALCULATE ( DISTINCTCOUNT ( Answers[Answer] ); ALL ( Answers ) );
        1;
        IF ( VALUES ( Survey[RecipientID] ) = USERID; 1; BLANK () )
    )

 

 

  • Place this measure on the table visual filters and set it to is not blank.

Be aware that if the answer is given by more than 1 person this will return an error. If you select all the answers it will return the full table.

 

Check result attach (file is saved on June version)

 

Regards,

MFelix

 


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




ignas Member
Member

Re: Need help with filtering

Hey @MFelix 

Very nice try, but unfortunately it could not work in any way, because in my real dataset I have over 10mln entries and some of them are repeating. Any other solution with Dax that could work?
I have a solution where I create a separate table from all of these answers and it works just fine, but it consumes space for storing a copy of data in a separate table.
Thanks for helping.
Cheers
Ignas

Super User III
Super User III

Re: Need help with filtering

Hi @ignas ,

 

Not aware that you had such a big database, it was not clear on your message.

 

In my example it is also creating an additional table on the model, because looking at interactions between the visuals if they are from the same table they will filter out the information so you are being a victim of the setup of the data.

 

If you have a single table and you have a slicer and a chart when you apply filter on the slicer the corresponding visual will have the same filter you cannot dissociate one from the other since they are within the same table.

 

Regards,

MFelix

 

 


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Iamnvt Member
Member

Re: Need help with filtering

hi,

 

maybe you can create another visual for "ddddd" filter alone, and hide the "ddddd" option from the slicer.

Create this measure:

Measure = CALCULATE(COUNT(Survey[RecipientID]), Survey[Answer] = "ddddd")

to do the visual filter for "ddddd" table.

unlink the visual interaction between slicer and "ddddd" table.

 

you can check PBI here:

https://1drv.ms/u/s!Aps8poidQa5zk6p365C2AuB5YH8Lxg

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors