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.
I have a plot that shows 3 different series of the same data that are filteres based on 3 groups of slicers.
Each series can be activated/deactivated separately, using a slicer on , so I can choose what combination of the 3 series I want to see.
I have a column of data and slicer in each series called "AgeGroup". Based on that I can see if for example it is legal for the person to work or not (This is a categorical type of data). I call the measures that show this, "Legal?".
Now what I want is to be able to see the existing visualization (a scatter plot) only when ALL THE ACTIVATED series have the same result in their "Legal?" measure.
For example if I activate Series 2 and 3, I only want to be able to see the plot, if the contents of "Legal? 2" and "Legal? 3" have the same values. If Series 1 and 3 are activated, the comparison should be between "Legal? 1" and "Legal? 3" and so on.
Link to the sample (and a bit messy) PBIX file: https://tinyurl.com/463n3rzs
@OwenAuger I feel you may have an answer for this familiar file 🙂
Solved! Go to Solution.
Hi again @MoeData
A bit busier than expected so delayed in replying - thanks for your patience!
I have attached a PBIX with a possible solution. A few comments on this:
Otherwise, the setup of the visuals is basically unchanged.
I did tinker a bit with the visuals in my PBIX as I was testing things, so I may have made some slight changes.
Here is the udpated DAX code:
Legal? 1 =
SWITCH (
SELECTEDVALUE ( DimEmployees1[AgeGroup] ),
"Infant", "No",
"Normal", "Yes",
"Fossil", "Yes"
)
// 2 & 3 similar
Legal Values All Same =
VAR LegalValues =
SELECTCOLUMNS (
ALLSELECTED ( Series ), -- Restore overall Series filter
"@Legal",
SWITCH (
Series[Series],
"Series 1", [Legal? 1],
"Series 2", [Legal? 2],
"Series 3", [Legal? 3]
)
)
VAR LegalValuesCount =
COUNTROWS ( DISTINCT ( LegalValues ) )
RETURN
LegalValuesCount = 1
EducationYear Value Base =
IF (
[Legal Values All Same]
&& NOT ISEMPTY ( DimSalary )
&& [Is Salary Count above threshold],
SELECTEDVALUE ( DimEmployees[EducationYear] )
)
Salary Value Base =
IF (
[Legal Values All Same]
&& [Is Salary Count above threshold],
SELECTEDVALUE ( DimSalary[Salary] )
)
Series Selection =
SELECTEDVALUE ( Series[Series] )
EducationYear Value =
SWITCH (
[Series Selection],
"Series 1",
CALCULATE (
[EducationYear Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees1[EmployeeID] )
),
"Series 2",
CALCULATE (
[EducationYear Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees2[EmployeeID] )
),
"Series 3",
CALCULATE (
[EducationYear Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees3[EmployeeID] )
)
)
Salary Value =
SWITCH (
[Series Selection],
"Series 1",
CALCULATE (
[Salary Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees1[EmployeeID] )
),
"Series 2",
CALCULATE (
[Salary Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees2[EmployeeID] )
),
"Series 3",
CALCULATE (
[Salary Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees3[EmployeeID] )
)
)
Hopefully that's what you were looking for!
Please test it out, and post back any issues or other thoughts.
Regards,
Owen
Hi again @MoeData
A bit busier than expected so delayed in replying - thanks for your patience!
I have attached a PBIX with a possible solution. A few comments on this:
Otherwise, the setup of the visuals is basically unchanged.
I did tinker a bit with the visuals in my PBIX as I was testing things, so I may have made some slight changes.
Here is the udpated DAX code:
Legal? 1 =
SWITCH (
SELECTEDVALUE ( DimEmployees1[AgeGroup] ),
"Infant", "No",
"Normal", "Yes",
"Fossil", "Yes"
)
// 2 & 3 similar
Legal Values All Same =
VAR LegalValues =
SELECTCOLUMNS (
ALLSELECTED ( Series ), -- Restore overall Series filter
"@Legal",
SWITCH (
Series[Series],
"Series 1", [Legal? 1],
"Series 2", [Legal? 2],
"Series 3", [Legal? 3]
)
)
VAR LegalValuesCount =
COUNTROWS ( DISTINCT ( LegalValues ) )
RETURN
LegalValuesCount = 1
EducationYear Value Base =
IF (
[Legal Values All Same]
&& NOT ISEMPTY ( DimSalary )
&& [Is Salary Count above threshold],
SELECTEDVALUE ( DimEmployees[EducationYear] )
)
Salary Value Base =
IF (
[Legal Values All Same]
&& [Is Salary Count above threshold],
SELECTEDVALUE ( DimSalary[Salary] )
)
Series Selection =
SELECTEDVALUE ( Series[Series] )
EducationYear Value =
SWITCH (
[Series Selection],
"Series 1",
CALCULATE (
[EducationYear Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees1[EmployeeID] )
),
"Series 2",
CALCULATE (
[EducationYear Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees2[EmployeeID] )
),
"Series 3",
CALCULATE (
[EducationYear Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees3[EmployeeID] )
)
)
Salary Value =
SWITCH (
[Series Selection],
"Series 1",
CALCULATE (
[Salary Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees1[EmployeeID] )
),
"Series 2",
CALCULATE (
[Salary Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees2[EmployeeID] )
),
"Series 3",
CALCULATE (
[Salary Value Base],
USERELATIONSHIP ( DimEmployees[EmployeeID], DimEmployees3[EmployeeID] )
)
)
Hopefully that's what you were looking for!
Please test it out, and post back any issues or other thoughts.
Regards,
Owen
@OwenAuger
Hi Owen,
Super! Yes, that does what I am looking for.
Thanks for the solutions! and also for well-documented and clear explanations 🙂
I really like your approach.
@OwenAuger wrote:
- I also simplified the code of the Legal measures using SWITCH. By the way, these measures will return blank if more than one AgeGroup is selected - just checking is this what you want?
Actually, I should make this filter a single-select one, so more than one is not possible to choose.
Best regards,
Moe
Hi @OwenAuger
Each of the three [Legal? X] measures depend only on the value of DimEmployeesX[AgeGroup] so you are correct, it is a typo. Thanks 🙂
Best,
Moe
Hi Moe 🙂
Just working through this - a bit late here so may have to reply in the morning 🙂
Will post back in the morning my time.
All the best,
Owen
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |