How to fetch column from another Dimension table without relationship

Question

I have 2 dimension tables with no relation between them.

1 is Product Dimension table as

ProdCode | ValidStartDate | ValidEndDate

XX | 2012-01-01| 2016-12-31
XX | 2017-01-01| 2017-12-31
XX | 2018-01-01| 2020-12-31

2nd is Time table

Year | IsCurrent

2012 | 0
2013 | 0
2014 | 0
2015 | 0
2016 | 0
2017 | 0
2018 | 0
2019 | 0
2020 | 1

I need to create a calculated column in Product table to show IsCurrent column from Time Table wrt the year selected.

I tried with CALCULATE but it expects one of the aggregate functions which i can not use because i want to show value in current row context.

for example:

IsCurrent =
CALCULATE(
    MAXA('Time'[IsCurrent]),
    FILTER(
        'Time',
        'Time'[Year] >= YEAR(Product[ValidStartDate]) 
            && 'Time'[Year] <= YEAR(Product[ValidEndDate])
    ) 
)

This always gives me Max value from the range satisfied for example in case of 1st record (2012- 2016) shows 2016 always but I want to show respective current row year from Time table.

Please suggest.

Thank you.

mkRabbani · Answer 1 · 2020-09-21T10:09:28.787

1

Try this below Measure script-

Measure

IsCurrent = 
CALCULATE(
    MAXA('Time'[IsCurrent]),
    FILTER(
        'Time',
        'Time'[Year] >= YEAR(MIN(Product[ValidStartDate]))
            && 'Time'[Year] <= YEAR(MIN(Product[ValidEndDate]))
    ) 
)

Custom Column

IsCurrent_column = 

var current_start_year = YEAR(Product[ValidStartDate])
var current_end_year = YEAR(Product[ValidEndDate])

RETURN
CALCULATE(
    MAXA('time'[IsCurrent]),
    FILTER(
        'time',
        'time'[Year] >= current_start_year
            && 'time'[Year] <= current_end_year
    ) 
)

Here is the output-

edited Sep 21 '20 at 10:09

answered Sep 18 '20 at 08:49

mkRabbani

16,295
2
15
24

Custom column i was already trying but did not work but seems Measure works in my case. Let me try. Thanks for the answer. – LearnSQL Sep 18 '20 at 13:46
Do you have any idea why Custom column logic is not working. – LearnSQL Sep 21 '20 at 10:01
Change the table name form "Product Time" to "Time". If issue still exista,Provide the code here if possible. – mkRabbani Sep 21 '20 at 10:04
Hi @LernSQL i have updated the answer. Please check. – mkRabbani Sep 21 '20 at 10:10
Hi @mkRabbani, Thanks for the update. but issue was not with table name. i had corrected it accordingly. The issue is it always returns max year from that range. for example in case of 2012-2016 range even if i select 2012 in slicer it returns 2016. It works fine with single year range value. – LearnSQL Sep 21 '20 at 11:45
@LearnSQL nice to hear that. You can accetp the answer and put an up vote if it really helped :) – mkRabbani Sep 21 '20 at 11:47
It does not work for range values. as i specified, in case of 2012-2016 range even if i select 2012 in slicer it returns 2016 instead of 2012. It must always return the year selected in slicer. – LearnSQL Sep 21 '20 at 11:50
@LearnSQL Hahah, for accepting or giving an up vote, this is not always required the exact solution. Fact is - it drive you to your solution or not :) – mkRabbani Sep 21 '20 at 11:52
done. Please update your answer if you could solve for range values. Thanks. – LearnSQL Sep 21 '20 at 12:00
Show your expected output from the above sample data. – mkRabbani Sep 21 '20 at 12:07
Hi, If i select 2013 from year Slicer I would like to see below output ProdCode | ValidStartDate | ValidEndDate | Year Selected | Is Current XX | 2012-01-01| 2016-12-31 | 2013 | 0 where Year Selected and Is Current columns are taken from Time table. Thanks for checking. – LearnSQL Sep 21 '20 at 12:28
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/221815/discussion-between-mkrabbani-and-learnsql). – mkRabbani Sep 21 '20 at 13:44

score 1 · Answer 2 · answered Sep 21 '20 at 16:56

1

Create this below measure-

in_range = 

VAR selected_year = SELECTEDVALUE('time'[Year])

RETURN IF(
    YEAR(MIN('product'[ValidStartDate])) <= selected_year
        && YEAR(MIN('product'[ValidEndDate])) >= selected_year
    ,
    1,
    0
)

This will give you a output as below-

Now you can add a Visual Level filter using this above measure so that the row wonly show when in_range = 1. This filter will only keep your expected rows in the table.

answered Sep 21 '20 at 16:56

mkRabbani

16,295
2
15
24

I tried but SELECTEDVALUE() is not recognized in my solution. i am using SSAS tabular model as a source. – LearnSQL Sep 22 '20 at 07:07
That's an issue for SSAS tabular model :( – mkRabbani Sep 22 '20 at 07:18

How to fetch column from another Dimension table without relationship

2 Answers2