how to make a custom column for comparing with in between of 2 numbers?

[Anonymous]

Hi gurus,

I was wondering is it possible to create a custom column which compares if a value existed between two other numbers rather than comparing >, =, <, <=, >=?

so I have 2 tables, one table is like a DIM table and I want to create a custom column that shows a string instead if a number in my Fact table existed in between of each range of numbers?

How to do it?

Thanks

[Anonymous]

Hi @Anonymous 

I think I know what you are after.

You can set the lower than and greater than to variables, these variables can be either a measure you set or a calculated, or taken from the table. then if the number in that row is between those 2 numbers display text. Again this can be set or taken from the fact table.

Show text = IF(Table_A[ID] >= [.Lower Between] && Table_A[ID] <= [.Higher Between],Table_A[Firstname],BLANK())

I did a mock up using 2 measured I created. if the ID of the row is between the lower and higher number set then their name is displayed.

I hope this helps.

[Anonymous]

@Anonymous  Thanks!

I think I should have been more clear because this is not exactly the solution!

My fact data is in a different table and the dimension that it should be compared to is in another table so it is not  possible to just go ahead with this solution

I would appreciate the gurus reply please?

Many thanks

[Stachu]

With DAX you can reference different tables (if active join exist between them), with RELATED
https://docs.microsoft.com/en-us/dax/related-function-dax
and RELATEDTABLE
https://docs.microsoft.com/en-us/dax/relatedtable-function-dax

[Anonymous]

@Stachu  May you please explain how does this relate to my question?

Thanks

[Stachu]

In general, in DAX, comparing values from different tables is not really an issue. If you want to reference multiple tables you either rely on the joins, or use the functions I posted earlier.

If you provide anonymised sample of your data I could be more specific to your case

[Stachu]

your desctiption is not very clear, but it sounds doable you just do the regular comparison of < > joined with the AND operator

Cna you share few sample rows from your tables to visualize the problem better?