Forum Discussion

khajvd's avatar
khajvd
Regular Visitor
8 years ago

Inactive relationship with calculated column.

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,

Need Help

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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's avatar
      khajvd
      Regular Visitor
      8 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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