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

Selected Measure (Parameter) to exclude Categories

Hi

 

I have a parameted that switches the left column of a table to cycle through D&I elements. 

 

Each of the D&I elemtnes (Age / Gender / Ethnicity etc) have "Not Captured" - This is the bulk so charts etc do not look good so I want to exclude them - If in the visual filters I exclude for each category I lose most data i.e where someone has answered on but not another they are completely excluded.

 

I wanted to do a measure like the following but as I cant get it to work for the life of me - Any help appreciated:

_INC/EXC = 

switch

(

true(),

and(selectedvalue([parameter]) = "Gender", [Gender] = "Not Disclosed"),"Exc",

and(selectedvalue([parameter]) = "Nationality", [Nationality] = "Not Disclosed"),"Exc",

... , 

... , 

"Inc"

)

 

 

1 ACCEPTED SOLUTION
olgad
Power Participant
Power Participant

Hi, 

_INC/EXC =

switch

(max('Your Parameter Table Name'[Parameter Order]),
1, if(SELECTEDVALUE('Table'[Gender])="Not Disclosed", "Exc", "Inc"),
2, if(SELECTEDVALUE('Table'[Nationality])="Not Disclosed", "Exc", "Inc"))

Place it onto Visual Filters, Contain Inc
Just in case shoing to you how it goes with my data
olgad_0-1674558750318.png
_INC/EXC =

switch

(max('Field Parameter'[Parameter Order]),
1, if(SELECTEDVALUE('Product'[Category])="Mix", "Exc", "Inc"),
2, if(SELECTEDVALUE('Product'[Segment])="Extreme", "Exc", "Inc"))

olgad_1-1674558854934.png

Please kudo and accept the solution if it helps:)


View solution in original post

4 REPLIES 4
MITeam
New Member

Hi.

Thanks for your reply

The problem was that as I was using a parameter as the driver and each category in the parameter had its own "Not Disclosed" I eneded up doing a long measure and adding this measure to the table and filtered the measure on "Inc" then removed the measure fomr the table... It gave me a headache but I got there 

__Inc/Exc = SWITCH(true(),
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Age Category]",SELECTEDVALUE('-_Div'[__Age Category])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Disability]",SELECTEDVALUE('-_Div'[__Disability])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Ethnic Group]",SELECTEDVALUE('-_Div'[__Ethnic Group])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Gender Identity]",SELECTEDVALUE('-_Div'[__Gender Identity])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Nationality]",SELECTEDVALUE('-_Div'[__Nationality])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Neurodiverse]",SELECTEDVALUE('-_Div'[__Neurodiverse])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Parents]",SELECTEDVALUE('-_Div'[__Parents])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Qualification]",SELECTEDVALUE('-_Div'[__Qualification])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Religion]",SELECTEDVALUE('-_Div'[__Religion])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__School Meals]",SELECTEDVALUE('-_Div'[__School Meals])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__School Type]",SELECTEDVALUE('-_Div'[__School Type])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Sex]",SELECTEDVALUE('-_Div'[__Sex])="Not Captured"),"Excl",
and(SELECTEDVALUE('Z. Diversity'[Z. Diversity Fields])="'-_Div'[__Sexual Orientation]",SELECTEDVALUE('-_Div'[__Sexual Orientation])="Not Captured"),"Excl",
"Incl")
olgad
Power Participant
Power Participant

Hi, 

_INC/EXC =

switch

(max('Your Parameter Table Name'[Parameter Order]),
1, if(SELECTEDVALUE('Table'[Gender])="Not Disclosed", "Exc", "Inc"),
2, if(SELECTEDVALUE('Table'[Nationality])="Not Disclosed", "Exc", "Inc"))

Place it onto Visual Filters, Contain Inc
Just in case shoing to you how it goes with my data
olgad_0-1674558750318.png
_INC/EXC =

switch

(max('Field Parameter'[Parameter Order]),
1, if(SELECTEDVALUE('Product'[Category])="Mix", "Exc", "Inc"),
2, if(SELECTEDVALUE('Product'[Segment])="Extreme", "Exc", "Inc"))

olgad_1-1674558854934.png

Please kudo and accept the solution if it helps:)


Looks cleaner than the solution I managed - Thankyou

ADPowerBI1
Resolver II
Resolver II

I belive you could just unselect blank values from the filter pane.

ADPowerBI1_1-1674558830903.png

 

Have you tried this for each of the categories? 

Let me know if this works, if not I'll have a further look. Thanks and good luck! 🙂

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.