Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MITeam
Regular Visitor

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
Super User
Super User

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:)



DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

4 REPLIES 4
MITeam
Regular Visitor

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
Super User
Super User

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:)



DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Looks cleaner than the solution I managed - Thankyou

ADPowerBI1
Responsive Resident
Responsive Resident

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.