using a slicer to control which attributes a table is filter by

[KBD]

Hello All:

I have transaction data, lets say it is petty cash purchases.  

I am using slicers to filter the table on the fly.

The usual slicers, department,  party making purchase, day of week,  purchase date, merchant.

This works just fine.

On top of this, I need to add, basic data quality , check for attributes that are empty.

Ie.  department,  party making purchase,  merchant, description.  A half dozen attribues in total.

Important:  the filter for empty attibutes shold not "blow away" other slicer filters set by the user.

Issue:

Cannot, after reading various posting and experimenting cannot get it to work.

What I have done.

Create a Field parameter  

missingDetails = {
("ACCOUNT NAME", NAMEOF('PCard_Trans'[ACCOUNT NAME]), 0),
("EMPLOYEE ID", NAMEOF('PCard_Trans'[EMPLOYEE ID]), 1),
("MERCHANT NAME", NAMEOF('PCard_Trans'[MERCHANT NAME]), 2),
("TRANSACTION DATE", NAMEOF('PCard_Trans'[TRANSACTION DATE]), 3),
("MCC", NAMEOF('PCard_Trans'[MCC]), 4),
("EXPENSE DESCRIPTION", NAMEOF('PCard_Trans'[EXPENSE DESCRIPTION]), 5)
}

create a slicer for missingDetails.  so the user can select the attribute to filter on.

Added a textbox that displays the field selected.  

above works I can select fields in missDetails slicer and the text box shows the field selected.

Now I believe I need a measure that will calc'ed based on the attribute selected and 

the value of the attirbute by row.

Have made two attempts as follows:.

ShowIfEmpty =
VAR SelectedFieldVal = SELECTEDVALUE( missingDetails[missingDetails Fields] )
RETURN
IF(ISBLANK(SelectedFieldVal), 1, 0)

showIfEmptyII = IF(ISBLANK(SELECTEDVALUE('missingDetails'[missingDetails Fields]) ), 1, 0 )

These measures always return zero, therefore useless for filtering.

Other attempts yeild this error:

"column is part of a composite key, but not all columns of the composite key are included in the expression"

I am at a stand still, no idea how to proceed.

Thanks for your attention to this matter.

KD

[v-agajavelly]

Hi @KBD ,

Just following up on this. Were you able to test the suggested approach? If it worked, marking the helpful reply as a solution would benefit others with a similar scenario.

If you're still facing any issues or seeing unexpected behavior, please share a small example or details we’re happy to help further.

Thanks,
Akhil.

[KBD]

Followed up on the suggestions and nothing worked.

My requirement is very simple  do NOT understand why Power BI makes this difficult.

KBD

[MFelix]

Hi @KBD,

Apologies for the additional questions but just want to understand that I have the proper understanding of your need.

In you table 'PCard_Trans' there are some atributes that are blank and you want to return the rows that have that specific attribute blank out, in this case you want to select on the field parameter the specific attributes that are blank is that it?

Can there be more than one attribute selected?

The final result is a table with the information displaying the information that is missing or an ID, or another way to identify in wich rows they are missing?

Can you provide an example on the expected result.

Once again I apologize for the question but maybe I'm missing something on your request.

[KBD]

I have records for transactions that may have missing data.  

Ie transaction date could be missing (null)

    expense description could be missing (null)

    merchant name could be missing (null)

   or

   expense description and merchant name could be missing.

To select the attibute I want to look at 

I have a field selector as follows:

missingDetails = {

    ("ACCOUNT NAME", NAMEOF('PCard_Trans'[ACCOUNT NAME]), 0),

    ("EMPLOYEE ID", NAMEOF('PCard_Trans'[EMPLOYEE ID]), 1),

    ("MERCHANT NAME", NAMEOF('PCard_Trans'[MERCHANT NAME]), 2),

    ("TRANSACTION DATE", NAMEOF('PCard_Trans'[TRANSACTION DATE]), 3),

    ("MCC", NAMEOF('PCard_Trans'[MCC]), 4),

    ("EXPENSE DESCRIPTION", NAMEOF('PCard_Trans'[EXPENSE DESCRIPTION]), 5)

}

Field selector is in a slicer.

If the user selects Employee Id, I want the table to be filtered by 

Filter on Visual

Employee Id is Null or Employee ID = ""

If the user selects MCC, I want the table to be filtered by 

Filter on Visual

MCC is Null or MCC = ""

I will work on some mocked up data that I can post.

Thanks for your interest in this issue.

KBD

[MFelix]

Hi @KBD ,

I believe I was able to mockup your model I have the following table:

Has you can see I have several fields that are blanked I also created a Parameter table like the one you have:

However in this table I have added a new column Name that is just the parameter value this is just to simplify the next measure:

Now I added this measure:

Filter Blanks = VAR __SelectedValue =
		SELECTCOLUMNS(
			SUMMARIZE(
				Parameter,
				Parameter[Parameter],
				Parameter[Parameter Fields]
			),
			Parameter[Parameter]
		)

    var ColumnValues = VALUES(Parameter[Name])
	
    VAR temptable =
		SELECTCOLUMNS(
			PCard_Trans,
			PCard_Trans[Account Name],
			PCard_Trans[Employee ID],
			PCard_Trans[Expense Description],
			PCard_Trans[MCC],
			PCard_Trans[Merchant Name],
			PCard_Trans[Transaction Date],
			"Blank count", 
              IF(
				"Account Name" IN ColumnValues,
				 ISBLANK(PCard_Trans[Account Name])
			) +
			IF(
				"Employee ID" IN ColumnValues,
				ISBLANK(PCard_Trans[Employee ID])
			) +
			IF(
				"Expense Description" IN ColumnValues,
				 ISBLANK(PCard_Trans[Expense Description])
			) +
			IF(
				"Transaction Date" IN ColumnValues,
				 ISBLANK(PCard_Trans[Transaction Date])
			) +
			IF(
				"MCC" IN ColumnValues,
				 ISBLANK(PCard_Trans[MCC])
			) +
			IF(
				"Merchant Name" IN ColumnValues,
				 ISBLANK(PCard_Trans[Merchant Name])
			)
		)
		RETURN
			MAXX(
				temptable,
				[Blank count] 
			)

Then if you add this to the matrix and select the values that are different from you will get the expected result:

Please let me know if this works per your needs.

Be aware that the table visual is not based on actual columns and not on the parameters values table, because if you use the parameter table it will only return that specific column and everything will go blank.

If you are using this parameter table only for this specific need I suggest that you replace this by a disconnected table with the columns names making it easier to make the measure since you don't need to have the workaround for the selection of the names.

[KBD]

Miguel:

I worked replicated you suggestion and we are so close.   Much close than I ever would have gotten. 

My mock up data

Trans Id	Transaction Date	MCC	Merchant Name	Employee Id	Employee Name	Expense Description	Amount
000009842692378	2/3/2025	5942	Book store	16252	Donald Duck		84.10
000011414319771	6/6/2025	5065	Electrical Parts	97427	Micky Mouse	extension cord	-71.38
000009452479682	8/8/2025	5251	Local Hardware Store	930758	Superman	KEYS	146.08
000011361076005	11/11/2025	5251	Local Hardware Store	95464	Batman	Caulking	449.90
000011387100331	10/10/2025	5251	Local Hardware Store			Hammer	163.85
000011409252487	12/12/2025	5251	Local Hardware Store	97678	Green Hornet	Drill Bit	111.82
000011476894343	1/10/2026		Local Hardware Store	096284	Poison Ivy	1"pip	-26.58
000009747145596	1/12/2026	5251	Local Hardware Store		**bleep** Tracy		118.63
000011569009247			Local Hardware Store	94474	Wonder Woman	Adjustable Wrench	185.18
000011692905669	3/10/2026	5251	Local Hardware Store	11066	Under Dog		144.84
000011872913685	3/17/2026	5039	Plumbing Supply			PUMP PARTS	62.99
000011878834281	4/4/2025	5039	Plumbing Supply	930276	Spider Man	PUMP PARTS	37.56
000011878834277		5039	Plumbing Supply	95455	The Flash		12.57
000010078708642	5/5/2025	5039	Plumbing Supply		The Flash	MISC PLUMBING SUPPLIES	79.51

Parameter field:

fields = {
    ("Employee Id", NAMEOF('Sheet1'[Employee Id]), 0),
    ("Employee Name", NAMEOF('Sheet1'[Employee Name]), 1),
    ("Expense Description", NAMEOF('Sheet1'[Expense Description]), 2),
    ("MCC", NAMEOF('Sheet1'[MCC]), 3),
    ("Merchant Name", NAMEOF('Sheet1'[Merchant Name]), 4)
}

With addition attribute as suggested

fieldName = fields[fields]

Looks like this 

Now for the measure:

Filter Blanks = VAR __SelectedValue =
		SELECTCOLUMNS(
			SUMMARIZE(
				fields,
				fields[fields],
				fields[fields Fields]
			),
			fields[fields]
		)

    var ColumnValues = VALUES(fields[fieldName])
	
    VAR temptable =
		SELECTCOLUMNS(
			Sheet1,
			Sheet1[Employee Name],
			Sheet1[Employee Id],
			Sheet1[Expense Description],
			Sheet1[MCC],
			Sheet1[Merchant Name],
			Sheet1[Transaction Date],
			"Blank count", IF(
				"Employee Name" IN ColumnValues,
				1 * ISBLANK(Sheet1[Employee Name])
			) +
			IF(
				"Employee ID" IN ColumnValues,
				1 * ISBLANK(Sheet1[Employee ID])
			) +
			IF(
				"Expense Description" IN ColumnValues,
				1 * ISBLANK(Sheet1[Expense Description])
			) +
			IF(
				"Transaction Date" IN ColumnValues,
				1 * ISBLANK(Sheet1[Transaction Date])
			) +
			IF(
				"MCC" IN ColumnValues,
				1 * ISBLANK(Sheet1[MCC])
			) +
			IF(
				"Merchant Name" IN ColumnValues,
				1 * ISBLANK(Sheet1[Merchant Name])
			)
		)
		RETURN
			MAXX(
				temptable,
				[Blank count] 
			)

Above measue is created in my transaction table.  Do no know if that matters.

Add above measure to my table , my viz.

Create a filter on the viz 

Filter Blanks greater than zero.

The above works.  It even works if I select multiple appributes

Ie.  Employee Name & Expense Description.

Very Nice

[KBD]

continuing on the above

Problem:

If I clear the attribute selection  clear fields 

Not all records are displayed  

Get a table that looks like this.

only the records with missing data are showing

And there is an extra row at the top.   Don't know where that is coming from.

So I tried to fix this issue .

Tried to modify the measure so that if no attributes are select Filter Blanks is set to zero on all rows 

Filter Blanks II = VAR __SelectedValue =
		SELECTCOLUMNS(
			SUMMARIZE(
				fields,
				fields[fields],
				fields[fields Fields]
			),
			fields[fields]
		)

    var ColumnValues = VALUES(fields[fieldName])
	
    VAR temptable =
		SELECTCOLUMNS(
			Sheet1,
			Sheet1[Employee Name],
			Sheet1[Employee Id],
			Sheet1[Expense Description],
			Sheet1[MCC],
			Sheet1[Merchant Name],
			Sheet1[Transaction Date],
			"Blank count", 
			IF ( 
			    ISBLANK(ColumnValues ) || ColumnValues ==  ""  ,
				0
			)   + 			
			IF(
				"Employee Name" IN ColumnValues,
				1 * ISBLANK(Sheet1[Employee Name])
			) +
			IF(
				"Employee ID" IN ColumnValues,
				1 * ISBLANK(Sheet1[Employee ID])
			) +
			IF(
				"Expense Description" IN ColumnValues,
				1 * ISBLANK(Sheet1[Expense Description])
			) +
			IF(
				"Transaction Date" IN ColumnValues,
				1 * ISBLANK(Sheet1[Transaction Date])
			) +
			IF(
				"MCC" IN ColumnValues,
				1 * ISBLANK(Sheet1[MCC])
			) +
			IF(
				"Merchant Name" IN ColumnValues,
				1 * ISBLANK(Sheet1[Merchant Name])
			)
			
	
		)
		RETURN
			MAXX(
				temptable,
				[Blank count] 
			)

This is my change

Wanted to set Filter Blanks to zero for all rows if nothing is selected in fields.

Does NOT work.

If I add to my table I get 

this is beyond me.

Many thanks for your attention to this matter.

Believe we are close.

KBD

[MFelix · Accepted solution]

Hi @KBD ,

For this you need to have a different syntax because when you do ColumnValues == "" or ISBLANK(ColumnnValues) this is expecting a single value to do the comparision since the no selection on a slicer correspond to selecting all then you have an error of multiple values supplied.

Other thing is that having everything selected on the slicer or no selection gives the same result so you have to change your measure to pick up the filtering.

For this try the following code:

Filter Blanks II = VAR __SelectedValue =
		SELECTCOLUMNS(
			SUMMARIZE(
				fields,
				fields[fields],
				fields[fields Fields]
			),
			fields[fields]
		)

    var ColumnValues = VALUES(fields[fieldName])
	
    VAR temptable =
		SELECTCOLUMNS(
			Sheet1,
			Sheet1[Employee Name],
			Sheet1[Employee Id],
			Sheet1[Expense Description],
			Sheet1[MCC],
			Sheet1[Merchant Name],
			Sheet1[Transaction Date],
			"Blank count", 
			IF ( 
			     ISFILTERED(fields[fields Fields]) ,
				 			
			IF(
				"Employee Name" IN ColumnValues,
				1 * ISBLANK(Sheet1[Employee Name])
			) +
			IF(
				"Employee ID" IN ColumnValues,
				1 * ISBLANK(Sheet1[Employee ID])
			) +
			IF(
				"Expense Description" IN ColumnValues,
				1 * ISBLANK(Sheet1[Expense Description])
			) +
			IF(
				"Transaction Date" IN ColumnValues,
				1 * ISBLANK(Sheet1[Transaction Date])
			) +
			IF(
				"MCC" IN ColumnValues,
				1 * ISBLANK(Sheet1[MCC])
			) +
			IF(
				"Merchant Name" IN ColumnValues,
				1 * ISBLANK(Sheet1[Merchant Name])
			)
			    ,1
		    )
        )
		RETURN
			MAXX(
				temptable,
				[Blank count] 
			)
            

In this case the ISFILTERED allows to show if you have any selection on the filter, be aware that in this case having no selection returns all rows having all values selected in the slicer returns the ones with blanks:

Concerning the first row on your example I was not able to replicate it but seems to be working fine on my side.

[KBD]

Miguel:

You are the man.

That worked.

Many thanks

KBD

[MFelix]

Hi @KBD ,

Thank you for you kind words, don't forget to accept the correct answer so it can help others even if it is your own answer.

[v-agajavelly]

Hi @KBD

Thanks  for @MFelix , @cengizhanarslan  & @danextian  the detailed solution and explanation here really helpful.

@KBD Just checking in were you able to try the suggested approach and does it resolve your scenario?

If you're still seeing any mismatch or the filter isn’t behaving as expected, feel free to share a quick example and we can help you fine-tune it further.

Thanks,
Akhil.

[KBD]

was traveling for a week.

following up on this now

KBD

[danextian]

Hi @KBD 

You cannot reference a field parameter table row directly. What you can do is write a conditional measure that references the field order. For example:

SWITCH (
    SELECTEDVALUE ( FieldParameter[Order] ),
    0, [Measure A],
    1, [Measure B],
    3, [Measure C]
)

[cengizhanarslan]

Please try the measure below:

Has Missing Detail =
VAR _Selected =
    SELECTEDVALUE ( missingDetails[missingDetails Fields] )
RETURN
    SWITCH (
        _Selected,
        "ACCOUNT NAME",
            IF ( ISBLANK ( MAX ( PCard_Trans[ACCOUNT NAME] ) ),       1, BLANK () ),
        "EMPLOYEE ID",
            IF ( ISBLANK ( MAX ( PCard_Trans[EMPLOYEE ID] ) ),        1, BLANK () ),
        "MERCHANT NAME",
            IF ( ISBLANK ( MAX ( PCard_Trans[MERCHANT NAME] ) ),      1, BLANK () ),
        "TRANSACTION DATE",
            IF ( ISBLANK ( MAX ( PCard_Trans[TRANSACTION DATE] ) ),   1, BLANK () ),
        "MCC",
            IF ( ISBLANK ( MAX ( PCard_Trans[MCC] ) ),                1, BLANK () ),
        "EXPENSE DESCRIPTION",
            IF ( ISBLANK ( MAX ( PCard_Trans[EXPENSE DESCRIPTION] ) ), 1, BLANK () ),
        1
    )

Add this measure to Filters on this visual on your transaction table and set it to is 1.

[KBD]

I have been rude.   Forgot to thank you for your suggestion.

Thanks 

But do not believe it works.  

Please refer to other comments

KBD

[KBD]

If I do the following:showIfEmptyfixed = VAR __SelectedValue = SELECTEDVALUE( missingDetails[missingDetails Fields] ) RETURN SWITCH( __SelectedValue , "'PCard_Trans'[ACCOUNT NAME]", "ACCOUNT NAME" , "'PCard_Trans'[EMPLOYEE ID]", "EMPLOYEE ID" , "'PCard_Trans'[MERCHANT NAME]", "MERCHANT NAME" , "'PCard_Trans'[TRANSACTION DATE]", "TRANSACTION DATE", "'PCard_Trans'[MCC]", "MCC", "'PCard_Trans'[EXPENSE DESCRIPTION]", "EXPENSE DESCRIPTION", "None Selected" ) 

I can get back the field I selected in my slicer.  Notice the field name must be fully specified  Ie.   "'PCard_Trans'[ACCOUNT NAME]"

I see the corresponding text in my table.

This tells me:

SELECTEDVALUE(

              missingDetails[missingDetails Fields]

is returning the expected value.

 

If I use:

showIfEmptyfixed = VAR __SelectedValue =
    SELECTEDVALUE( 
              missingDetails[missingDetails Fields]
    )
  RETURN   
SWITCH( __SelectedValue ,    
     "'PCard_Trans'[ACCOUNT NAME]",          SELECTEDVALUE( 'PCard_Trans'[ACCOUNT NAME] ) ,
     "'PCard_Trans'[EMPLOYEE ID]",           SELECTEDVALUE('PCard_Trans'[EMPLOYEE ID]  )   ,
     "'PCard_Trans'[MERCHANT NAME]",         SELECTEDVALUE('PCard_Trans'[MERCHANT NAME]  ) ,     
     "'PCard_Trans'[TRANSACTION DATE]",      SELECTEDVALUE( 'PCard_Trans'[TRANSACTION DATE]  ),
     "'PCard_Trans'[MCC]",                   SELECTEDVALUE( 'PCard_Trans'[MCC]  )    ,
     "'PCard_Trans'[EXPENSE DESCRIPTION]",  SELECTEDVALUE(  'PCard_Trans'[EXPENSE DESCRIPTION] ),
    "None Selected"  
  
  )I get values , but only on certain rows.    

do not see the pattern as to how the values are returned.

Following your suggestion I did the following:

showIfEmptyfixed = VAR __SelectedValue = SELECTEDVALUE( missingDetails[missingDetails Fields] ) RETURN SWITCH( __SelectedValue , "'PCard_Trans'[ACCOUNT NAME]", IF ( ISBLANK ( MAX( 'PCard_Trans'[ACCOUNT NAME] ) ) , 1, BLANK () ), "'PCard_Trans'[EMPLOYEE ID]", IF ( ISBLANK ( MAX('PCard_Trans'[EMPLOYEE ID] ) ) , 1, BLANK () ), "'PCard_Trans'[MERCHANT NAME]", IF ( ISBLANK ( MAX('PCard_Trans'[MERCHANT NAME] ) ) , 1, BLANK () ), "'PCard_Trans'[TRANSACTION DATE]", IF ( ISBLANK ( MAX( 'PCard_Trans'[TRANSACTION DATE] ) ), 1, BLANK () ), "'PCard_Trans'[MCC]", IF ( ISBLANK ( MAX( 'PCard_Trans'[MCC] ) ) , 1, BLANK () ), "'PCard_Trans'[EXPENSE DESCRIPTION]", IF ( ISBLANK ( MAX( 'PCard_Trans'[EXPENSE DESCRIPTION] ) ), 1, BLANK () ), "None Selected"

This returns  1 in the opposite pattern from the prior  measure code.

So no progress here.

I believe the Max and SelectedValue look at the whole table and not row by row.   Can anyone provide feed back on that.

[KBD]

created a measure as follows:

showIfEmptyfixed = VAR __SelectedValue =
    SELECTEDVALUE( 
              missingDetails[missingDetails Fields]
    )
  RETURN   
SWITCH( __SelectedValue ,    
     "ACCOUNT NAME",      IF ( ISBLANK ( MAX ( PCard_Trans[ACCOUNT NAME] ) ),       1, BLANK () ),
     "EMPLOYEE ID",       IF ( ISBLANK ( MAX ( PCard_Trans[EMPLOYEE ID] ) ),        1, BLANK () ),
     "MERCHANT NAME",     IF ( ISBLANK ( MAX ( PCard_Trans[MERCHANT NAME] ) ),      1, BLANK () ),
     "TRANSACTION DATE", IF ( ISBLANK ( MAX ( PCard_Trans[TRANSACTION DATE] ) ),   1, BLANK () ),
     "MCC",              IF ( ISBLANK ( MAX ( PCard_Trans[MCC] ) ),                1, BLANK () ),
     "EXPENSE DESCRIPTION", IF ( ISBLANK ( MAX ( PCard_Trans[EXPENSE DESCRIPTION] ) ), 1, BLANK () ),
    1   
  
  ) 

added showIfEmptyfixed  to the table   each row comes back as 1 (one)

so no progress   believe MAX looks at the whole table , so this will never work

KBD

Added 

[KBD]

Based on the articule Miguel Félix pointed me to I tried the following

Using the following DAX:

showIfEmptyfixed = VAR __SelectedValue =
    SELECTCOLUMNS (
        SUMMARIZE ( missingDetails, missingDetails[missingDetails], missingDetails[missingDetails Fields] ),
        missingDetails[missingDetails]
    )
  RETURN   
   __SelectedValue 

I get display field name, from the parameter table,  selected on each row in my table.   Progress

If I use the following:

showIfEmptyfixed = VAR __SelectedValue =
    SELECTCOLUMNS (
        SUMMARIZE ( missingDetails, missingDetails[missingDetails], missingDetails[missingDetails Fields] ),
        missingDetails[missingDetails]
    )
  RETURN   
  SWITCH( __SelectedValue ,    
     "ACCOUNT NAME",     ISBLANK(MAX ('PCard_Trans'[ACCOUNT NAME])),
     "EMPLOYEE ID",      ISBLANK(MAX('PCard_Trans'[EMPLOYEE ID])),
     "MERCHANT NAME",    ISBLANK(MAX('PCard_Trans'[MERCHANT NAME])),
     "TRANSACTION DATE", ISBLANK(MAX('PCard_Trans'[TRANSACTION DATE])),
     "MCC",              ISBLANK(MAX('PCard_Trans'[MCC])),
     "EXPENSE DESCRIPTION", ISBLANK(MAX('PCard_Trans'[EXPENSE DESCRIPTION])),
    FALSE()
  
  ) 

The values returned True/false  do NOT correspond to the attribute selected.   Strange

 Thanks for your attention to this matter.

KD

[MFelix]

Hi @KBD ,

The problem in your calculation is that field parameters table because of the way they are built do not return any values when you used SELECTEDVALUE on any of the columns that are crreated at the time. To do this you need to use a workaround described in this article.

https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/

[KBD]

did not put my code snippets in code boxes because the site kept saying that invalide HTML was generated😯