cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
khajvd Frequent Visitor
Frequent Visitor

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,

3 REPLIES 3
Phil_Seamark Super Contributor
Super Contributor

Re: Inactive relationship with calculated column.

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

khajvd Frequent Visitor
Frequent Visitor

Re: Inactive relationship with calculated column.

@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.

Community Support Team
Community Support Team

Re: Inactive relationship with calculated column.

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 345 members 3,410 guests
Please welcome our newest community members: