Forum Discussion

KBD's avatar
KBD
Icon for Helper III rankHelper III
6 months ago
Solved

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

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,...
Need Help
  • MFelix's avatar
    MFelix
    5 months ago

    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.

cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
6 months ago

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's avatar
    KBD
    Icon for Helper III rankHelper III
    5 months ago

    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's avatar
    KBD
    Icon for Helper III rankHelper III
    5 months ago

    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's avatar
    KBD
    Icon for Helper III rankHelper III
    5 months ago

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

    Thanks 

    But do not believe it works.  

    Please refer to other comments

     

    KBD