Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Return records by slicer selection

Hello All,

I have a table in my power bi desktop that has blank values in my id column(see below).I have a name slicer in my report. Now what i want is to return all the records have the same id as that of the name selected in slicer along with records that have blank id. I have been able to return the records with same id but am unable to return blank id one as well. 

Please see below.

Thanks in advance.

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    To create a calcualted table.

     

    Filter = VALUES(Table1[name])

    Then we can create a measure as below and make the table visual filtered by the measure.

     

    Measure = var selename = SELECTEDVALUE('Filter'[name])
var sameid = 
CALCULATE(MAX(Table1[id]),FILTER(ALL(Table1),Table1[name]=selename))
return
IF(MAX(Table1[id]) = BLANK() || MAX(Table1[id])=sameid,1,0)

     

     

    Regards,

    Frank

6 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    One sample for your reference.

     

    1. Create a calcualted table.

     

    Filter = VALUES(Table1[id])

    2. Create a measure and make the table visual filtered by the measure as below.

     

    Measure = var _id = MAX(Table1[id])
var _sele = SELECTEDVALUE('Filter'[id])
return
IF(ISBLANK(_id) || _sele=_id,1,0)

     

    Please find the pbix as attached.

     

    Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thankyou  v-frfei-msft  for the solution very much but I am filtering by Name column in my slicer . So what would i write in my measure in that case.

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous ,

         

        To create a calcualted table.

         

        Filter = VALUES(Table1[name])

        Then we can create a measure as below and make the table visual filtered by the measure.

         

        Measure = var selename = SELECTEDVALUE('Filter'[name])
var sameid = 
CALCULATE(MAX(Table1[id]),FILTER(ALL(Table1),Table1[name]=selename))
return
IF(MAX(Table1[id]) = BLANK() || MAX(Table1[id])=sameid,1,0)

         

         

        Regards,

        Frank