Return records by slicer selection

[Anonymous]

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.

[v-frfei-msft]

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]

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 · Accepted solution]

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

[Anonymous]

Thanks Again @v-frfei-msft for your solution. I tried doing this query by creating a measure 

id_selected = SelectedValue(table3[id], 0)

Then created an id table with unique values minus the blank one  and  a calculated table where

Calculated Table = filter(All(table3),table3[id] = id_selected || table3[id] = blank())

but I didnt get the desired result. I cant figure out why.

Also i created relationship between them and used bifilter mode.

Can you tell me why.

Also can you tell me how max function in working in your measure.

Regards,

Chahat

[v-frfei-msft]

Hi @Anonymous ,

Actually the data in calculated column / table will not be changed dynamiclly by the selected value in slicer. So here I create a table visual to work on it, not a calcualted table. 

Here the I get the id of current row by MAX function. 

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft  your solution is working fine but isnt there any other way of doing this since i want that when i dont select any name in my Name slicer, my complete table should be displayed as it is rather than showing only the blank id records only.

Also I am doing the filtering in two tables.

Regards 

Chahat