How to use the good column in a map table

[Anonymous]

Hello,

I have infoTable and a maptable linked via the column TimeBucket.

(see the structure of the map table)

So, I would like to set a slicer to select on distribution among (Flat, Front-Loaded, Back-Loaded and Bell) . Therefore, if I select Bell distribution and the in table 1 the Bucket time is 0-9%, I would like to have in the column distribution, the value 0.02, 10-10%, and so on.

How to do that?

Regards,

[v-zhenbw-msft]

Hi @Anonymous ,

Do you want to create a column in the map table like this?

If it can meet your requirement, you can refer the following steps,

1. Create a new calculate table and create two columns.

Table = DISTINCT('Table'[Time Bucket])

left = VALUE(LEFT([Time Bucket],FIND("-",[Time Bucket])-2))

right = 
var x = RIGHT([Time Bucket],FIND("-",[Time Bucket]))
return
VALUE(LEFT(x,FIND("%",x)-1))

2. Then we can create a calculate column in map table.

Column = CALCULATE(MAX('Table'[Time Bucket]),FILTER('Table','map table'[Bell]*100>='Table'[left] && 'map table'[Bell]*100<='Table'[right]))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared and describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please don't contain any Confidential Information or Real data in your reply.         

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Good morning,

What you are presenting is quite interesting but it is not what I need.  The table you see with the various percentage per distribution type is linked into my model by the time bucket field.

What I would like to do is to use a slicer to select among the distribution.  For example, imagine that I choose the Bell distribution.

So I wonder if there is a way to use only the bucket time field and the bell field, in a lookupvalue function to bring the good percentage into another table

For example, imagine that in a table A is linked to the map table below and that table A contains a Time Bucket field.  Then, if I select the Bell distribution via a slicer, and that in table A a record as the value 40-49%, so I want to bring the value of 0.22 into that table using a lookupvalue.  If a flat distribution is selected, I want to bring the value of 0.1 and so on.

So I wonder if there is a way to do that.

Regards,

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @Anonymous ,

We can create a calculate column in Table A.

Bell = LOOKUPVALUE('map table'[Bell],'map table'[Time Bucket],'Table A'[Time Bucket])

Or if you have one-to-many relationship between Table A and map table, you create a table visual and add the Bell column to Table A, like the following screenshot.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.