Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to create a measure that returns specific values from a column?

I want to use it as a slicer. I'm using direct query (Tabular) and cannot add custom columns to the model, so i can use measures only. I want the slicer to include values from column A, but only th...
Anonymous's avatar
Anonymous
Not applicable
6 years ago
Hi MartynRamsden

What do you mean by calculated table?
His do i do that in pbi?
MartynRamsden's avatar
MartynRamsden
Icon for Solution Sage rankSolution Sage
6 years ago

Hi Anonymous 

 

Go the Modeling tab, then click 'New table'. You'll then need to enter a DAX expression for new table.

Your expression could be something like this:

 

NewTable = 
FILTER( 
    ALL( Table1[ColumnA] ),
    Table1[ColumnA] IN VALUES( Table2[ColumnB] )
)

 

This new table would contain a single column containing the values from ColumnA in Table1 that also exisit in ColumnB in Table2.

You can then use the new column as a slicer in your report.

 

Note: since there are no relationships to the new table, your measures will need to reference the new column (probably using TREATAS, VALUES or SELECTEDVALUE) so the slicer selction can be taken into account.

 

There are plenty of resources available about how to use disconnected slicers. Here are a few:

 

Best regards,
Martyn


If I answered your question, please help others by accepting it as a solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hey MartynRamsden,

    Unfortunately, calculated tables are disabled as well.
    • MartynRamsden's avatar
      MartynRamsden
      Icon for Solution Sage rankSolution Sage
      6 years ago

      Hi Anonymous 

       

      In that case, I think your only option is to speak to the developer of the Tabular data model and ask them to add your calculated column.

       

      Best regards,
      Martyn


      If I answered your question, please help others by accepting it as a solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Thank you for the reply MartynRamsden i will try to open the tabular project myself. But what did you mean by the previous note? That besides the calculated column ill be needing a key?
    • MartynRamsden's avatar
      MartynRamsden
      Icon for Solution Sage rankSolution Sage
      6 years ago

      Hi Anonymous 

       

      I wanted to stress that, if you added a disconnected slicer, you would probably have to update your measures to take its current value into account. Since you can't use a disconnected slicer, you don't need to worry about it.

       

      Just thought of a solution which may work without you having to edit the model...

       

      Create a measure to identify if a given value in Table1[ColumnA] is also in Table2[ColumnB]:

      FilterColumnA = 
VAR SelColA = SELECTEDVALUE( Table1[ColumnA] )
VAR Result = 
IF ( 
    SelColA IN VALUES ( Table2[ColumnB] ),
    "Y",
    "N"
)
RETURN Result

       

      Add Table1[ColumnA] as a slicer on your report.

      Then add the new measure as a visual level filter set and set it to "Y".

       

      Your slicer will then only show values in ColumnA which also appear in ColumnB.

       

      Best regards,
      Martyn


      If I answered your question, please help others by accepting it as a solution.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you I will try that.