Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to have two slicers interact in the following way.
Ignoring the numbers for the sliding slicer currently shown, as ideally I would like it to be 0 to 100%
I have Applications and Groups. For a chosen Country and for a percentage of applications that come from that country, I want to display the data for the Groups that fit this criteria.
For example, if Group 1 has 6 applications, and 4 come from Canada and the other 2 come from France, the percentage of applications in Canada is 67%.
Group 2 has 4 applications, with 2 from Canada and 2 from France, so the percentage of applications is 50%.
If I choose Canada in the first slicer, then choose the range from 60%-100%, only the data for Group 1 should appear in my report.
My data is structured as follows:
Table 1
Application | Country |
1 | Canada |
2 | France |
3 | France |
4 | Canada |
5 | Canada |
6 | Canada |
7 | France |
8 | France |
9 | Canada |
10 | Canada |
Table 2
Application | Group |
1 | 1 |
2 | 1 |
3 | 1 |
4 | 1 |
5 | 1 |
6 | 1 |
7 | 2 |
8 | 2 |
9 | 2 |
10 | 2 |
I don't know how to go about doing this so that the percentage range is in that second slicer.
Thank you very much.
Solved! Go to Solution.
Hi @sheap069 ,
Please check:
1. Create a What-if Parameter.
2. Create a country table for slicer.
Country = DISTINCT(Applications[Country])
3. Create relationships.
4. Create a measure.
Measure =
VAR t =
ADDCOLUMNS ( 'Groups-Apps', "Country_", RELATED ( Applications[Country] ) )
VAR SelectedCountry =
SELECTEDVALUE ( Country[Country] )
VAR Count_ =
COUNTROWS ( t ) + 0
VAR Count_SelectedCountry =
COUNTROWS ( FILTER ( t, [Country_] = SelectedCountry ) ) + 0
VAR Percent_ =
DIVIDE ( Count_SelectedCountry, Count_, 0 )
RETURN
IF (
Percent_ >= MIN ( Percentage[Percentage] )
&& Percent_ <= MAX ( Percentage[Percentage] ),
1
)
5. Put the measure in needed visuals' "Filters on this visual" and set as " is 1".
PS: Based on the data you provided, group 1 meets your requirements, too. Please let me know, if there is any misunderstanding.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sheap069 ,
Please check:
1. Create a What-if Parameter.
2. Create a country table for slicer.
Country = DISTINCT(Applications[Country])
3. Create relationships.
4. Create a measure.
Measure =
VAR t =
ADDCOLUMNS ( 'Groups-Apps', "Country_", RELATED ( Applications[Country] ) )
VAR SelectedCountry =
SELECTEDVALUE ( Country[Country] )
VAR Count_ =
COUNTROWS ( t ) + 0
VAR Count_SelectedCountry =
COUNTROWS ( FILTER ( t, [Country_] = SelectedCountry ) ) + 0
VAR Percent_ =
DIVIDE ( Count_SelectedCountry, Count_, 0 )
RETURN
IF (
Percent_ >= MIN ( Percentage[Percentage] )
&& Percent_ <= MAX ( Percentage[Percentage] ),
1
)
5. Put the measure in needed visuals' "Filters on this visual" and set as " is 1".
PS: Based on the data you provided, group 1 meets your requirements, too. Please let me know, if there is any misunderstanding.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @AllisonKennedy
I would like to follow up on this topic - did you get a chance to review my updated PBIX file with the three tables I'm using? I still need to be displaying the columns within Groups as you brought up.
Thank you.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Okay, I've attached an Excel sheet here of the model and the desired solution.
As you've demonstrated, for Canada being selected with a range of 60%-100%, Group 3 meets the criteria. I would just need the data from column three to be in the output as a table, which I've made in the last tab of the sheet.
Thank you
Hello @AllisonKennedy @Ashish_Mathur I am writing to follow up on this. Have you been able to view the updated data set?
Thank you very much.
Hi @sheap069
Sorry for the delay, it has been a busy week. Please see attached file and let me know what changes you need. The attached only shows the data for the selected country, if you need to see all data for that group it will get more complex.
Final step would be to filter the visual to show only when 'Applications in Selected Percent Range' = "yes"
Cheers!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AllisonKennedy ,
No worries. Thank you for the PBIX. Yes, unfortunately I will need tables displaying information about the Applications and the Groups.
So I have three tables:
Application (data about Applications)
Group-App (to associate the Group ID with the App ID)
Group (data about Groups)
The data tables for Applications and Groups will be on other pages of my report.
I'm sending an updated PBIX so you can visually see the three tables and how they interact.
Thanks again,
Hello, @AllisonKennedy @Ashish_Mathur My apologies for the inconvenience. I am attaching the example PBIX file here with the updated data set in the Groups table.
And yes, one application ID will be associated to one single country.
Thank you again for your help.
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you for the PBIX. However, I forgot to mention that unfortunately the RELATED isn't working for me because an Application ID can actually be associated with more than one Group ID, so I'm not sure how to deal with this.
Regards.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Well, you obviously have not thought through before posting your dummy dataset. I cannot help you if you cannot share a proper dataset.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |