topic Re: Filtering data on multiple parameters in DAX Commands and Tips

Filtering data on multiple parameters

MBone — Mon, 08 Jul 2024 09:09:55 GMT

I have data that is structured as below:

Record ID	System Name For Variable	Value
1	LikesBananas	Yes
1	LikesOranges	No
1	HasPet	Yes
2	HasPet	Yes
2	LikesBananas	No
3	HasPet	No
3	LikesOranges	Yes
3	DrivesACar	No
3	PersonName	Johnny

Most are yes/no answers, which are the ones I care about.

And I have a table structured as below containing labels of answers.

System Name For Variable	User Friendly Name
LikesBananas	Does the person like bananas?
LikesOranges	Does the person like oranges?
HasPet	Does the person have a pet?
DrivesACar	Does the person drive a car?
PersonName	What is the name of the person?

What I want to be able to get out is filter out records on multiple parameters. For instance, I would like to find (the count of) all Record IDs where user likes bananas and drives a car. Or another example, what is the ratio of users that like oranges where users have a pet. Additionally, I would like to be able to have the user friendly name display instead of system name for the variable.

What is the best way to get this data out? I assume I would need some measure for each possible filter and work off of that, but is there a simpler way?

Re: Filtering data on multiple parameters

foodd — Mon, 08 Jul 2024 09:20:36 GMT

Hello MBone , and thank you for sharing a question with the Community. The following is informational. Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: Filtering data on multiple parameters

Anonymous — Tue, 09 Jul 2024 02:49:21 GMT

Hi,MBone

I am glad to help you.

According to your description, you want to know how to filter data on multiple parameters?

If I understand you correctly, then you can refer to my solution.

First, after connecting to the data source, please create a relationship.

Then you can open Power Query Editor to create several parameters as follows.

Then you can use the parameters to filter in the corresponding fields. Here I have made two small examples to describe the specific use of Parameters:

The first example is to find LikeBananas as Yes and HasPet as Yes:

Result :

The second example is to find LikeBananas as Yes and DrivesACar as No:

Result :

You can select specific values for filtering, or you can filter dynamically by modifying the value of Parameters.

The final result presented in the visualization：

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.