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

[Anonymous]

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 those that exist in column B, which is a column from a different table.

Please help.

[MartynRamsden]

Hi @Anonymous 

It's not possible to use a measure in a slicer.

I understand you're not able to add calculated columns but are you able to add a calculated table?

Best regards,
Martyn

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

[Anonymous]

Hi @MartynRamsden

What do you mean by calculated table?
His do i do that in pbi?

[MartynRamsden]

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:

• powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/ 
• www.kasperonbi.com/dynamic-data-comparisons-using-disconnected-slicers-treatas-and-inactive-relationships/ 

Best regards,
Martyn

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

[Anonymous]

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]

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]

Hey @MartynRamsden, i wasn't able to use the selectedvalue function. Pbi doesn't allow me to use it at thar scope i guess.

[Anonymous]

Thank you I will try that.

[Anonymous]

For tabular model connections you can't create columns or calculated tables.
All these thing you need to do it at model level only.
In cube create column or calculated table as per requirement and then use it in slicer.

Please check my blog related to tabular model.

https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...?


Thanks,
Pravin


If it resolves your problem mark it as a solution and give Kudos.

[Anonymous]

you want to use column B values only in slicer right?

Go to your model Click on new column

New column=lookupvalue(ColumnA,ColumnA,ColumnB)

or 

new column=

VAR SelColA = MAX( Table1[ColumnA] )
VAR Result = 
IF ( 
    SelColA IN VALUES ( Table2[ColumnB] ),
    Table1[ColumnA],
    Blank()
)
RETURN Result

use this column in slicer and filter out blank values from slicer.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

[Anonymous]

The lookup option returns an error because there's no relationship between the two tables.
The other option returns an error in Pbi for some reason, saying there's an error in the expression.

Here's the thing, I know the two tables aren't connected. But when I use a slicer to slice the fact table, based on a column from a table related to the fact, I want the other slicer that is actually a column in the fact table, to only display the values that exist in another column, from unrelated table.
Is that even possible?

[Anonymous]

try this,

New column=calculate(max(table1[col A]),contains(table1,table1[col A],table2[col B]))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

[Anonymous]

I tried that and it looked good in the tabular model, but in Pbi it throws "error in expression".
By the way, there was an error in the formula saying "contains has been used in a true/false expression that is used as a table filter expression", so i wrapped 'contains' with the function 'filter'.

I have to point out, only when I slice the fact table with the first slicer (i mentioned that in my previous post), the values in the unrelated table change, because they belong to the same table.
So, for example, my main slicer is 'Org', from a dimension called 'Org_Unit', that has another column 'Sub_Org'.
When I slice my dashboard with 'Org', I want the third slicer 'Actual_Org' that is a column in the fact itself, to only show the values in 'Sub_Org' that are the result if the 'Org' slicing.
Hope i make sense, if not I'll try and draw a general picture.

[Anonymous]

Anyone..?

By the way, here's a basic overview:

So, when I use 'Slicer 1' - the fact table and 'Slicer 2' change their values, accordingly.

The thing is I want 'Slicer 3', that has the same values like 'Slicer 2', to display only the current values of 'Slicer 2', and it doesn't.

Hope this makes sense.

[Anonymous]

Column = CONTAINS(Table2, Table2[Value], Table1[Value])

create this column and check is it working or not and then create measure on this.

Thanks,

Pravin

[Anonymous]

Hi @Anonymous, unfortunately this didn't work since there's no direct relationship between the two tables.

[Anonymous]

Thank you @Anonymous I will try that tomorrow since I'm not at work right now.
I added a picture to demonstrate my situation, I'd love you to check.

[Anonymous]

Hi @Anonymous 

create one measure to check if value is there in another table or not.

FilterColumnA =

VAR SelColA = SELECTEDVALUE( 'Table'[Column1] )

VAR Result =

IF (

SelColA IN VALUES ( Table2[Column1] ),

"Y",

"N"

)

RETURN Result

Then use you first table column and slicer and add measure to visual level filter and set it to Y.

[Anonymous]

if you want to create column at cube then use this dax

Column = IF('Table'[Column1] in VALUES(Table2[Column1]),'Table'[Column1],BLANK())

and use this in your report as slicer and filter about blank values.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hi @Anonymous I really appreciated your dedication!
The first attempt (with vars) seemed to work just fine in tabular, but returned an "error expression" in pbi.
The second attempt (column = if function) also seemed fine in tabular, but returned the following error in pbi: "mdxscript(model) (3,32) failed to resolve name 'SYNTAXERROR'. It is not a valid table, variable or function name.

[Anonymous]

Hi @Anonymous 

use The first solution which i have shared using measure and Some screenshots.

You don't need to do anything at cube. you can create that measure at power Bi side only.

and follow those steps.

You are getting error may be because of your SSAS version as Selectvalue() is not supported for versions less than 2016.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.