Inactive relationship with calculated column.

[khajvd]

Hi,
I have one fact table which contain SalesAccountNo ,OrderDate, and one date dimension table. which have inactive relationship with fact Orderdate.
I want to create tabular report with SalesacountNo. referring to Orderdate. and also want to filter report with Orderdate.
was trying to use lookupvalue but its not working.

Thanks,

[Phil_Seamark]

Hi @khajvd

If you would like to create calculations that use data over an inactive relationship, you need to use the USERELATIONSHIP function in the calculation.

eg. this calculated measure

Measure = 
    CALCULATE(
        COUNTROWS('fact'),
        USERELATIONSHIP(
            'fact'[OrderDate],
            'date'[OrderDate])
            )

The question is, why do you have an inactive relationship between your fact and date table?

[khajvd]

@Phil_Seamark I was able to create the measure using inactive relationship. but for the detail data i want 'salesaccountno' also in the tabular report. and same should filter using inactive relationship.

Now about the question why we have inactive relation ship:

 Basically  we dont want to give user two date(sales and order) in the filter pan. we only want to give them single date and based on the seletion on the slicer they can chose from sales or order date.

Every thing is working fine only problem i have with the detail data. how to show and filter them with inactive relationship.

[v-shex-msft]

Hi @khajvd,

Actually, I don't think phil's solution will suitable for your requirement.

USERELATIONSHIP function is work in measures, it not real change relationships, so you will get correct result on measure and get wrong result on visuals.

FOr your scenario, you need to manual active and disable relationship to apply these filter effect on all related tables.

Regards,

Xiaoxin Sheng