CODEWITHSUNDEEP
powerbidaxssas-tabular
LearnSQL
LearnSQL

Reputation: 47

How to fetch column from another Dimension table without relationship

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.

Upvotes: 1

Views: 406

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

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-

enter image description here

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.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

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-

enter image description here

Upvotes: 1

Related Questions