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 ...
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

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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