topic Re: How to create a measure that returns specific values from a column? in DAX Commands and Tips

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

Anonymous — Thu, 30 Jan 2020 09:55:25 GMT

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.

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

MartynRamsden — Thu, 30 Jan 2020 10:41:58 GMT

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.

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

Anonymous — Thu, 30 Jan 2020 10:56:29 GMT

Hi MartynRamsden

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

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

MartynRamsden — Thu, 30 Jan 2020 11:36:32 GMT

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.

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

Anonymous — Thu, 30 Jan 2020 11:43:02 GMT

Hey MartynRamsden,

Unfortunately, calculated tables are disabled as well.

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

MartynRamsden — Thu, 30 Jan 2020 11:46:09 GMT

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.

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

Anonymous — Thu, 30 Jan 2020 11:58:56 GMT

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?

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

MartynRamsden — Thu, 30 Jan 2020 13:37:50 GMT

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.

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

Anonymous — Sat, 01 Feb 2020 15:44:07 GMT

Thank you I will try that.

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

Anonymous — Sat, 01 Feb 2020 18:08:41 GMT

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/913784?

Thanks,
Pravin

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

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

Anonymous — Mon, 03 Feb 2020 08:54:06 GMT

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

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

Anonymous — Mon, 03 Feb 2020 08:56:40 GMT

Thanks for your reply, Anonymous.
Could you tell me how to do that, in the tabular model? I got a little confused.

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

Anonymous — Mon, 03 Feb 2020 09:52:32 GMT

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)

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/913784

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

Anonymous — Mon, 03 Feb 2020 10:36:53 GMT

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?

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

Anonymous — Mon, 03 Feb 2020 10:44:49 GMT

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/913784

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

Anonymous — Mon, 03 Feb 2020 12:28:41 GMT

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.

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

Anonymous — Wed, 05 Feb 2020 11:32:14 GMT

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.

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

Anonymous — Wed, 05 Feb 2020 09:12:23 GMT

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

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

Anonymous — Wed, 05 Feb 2020 11:34:27 GMT

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.

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

Anonymous — Thu, 06 Feb 2020 10:27:54 GMT

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