Desktop

[setis]

I have a table for [Cases] and another one for [Payments].

These tables are joined through CaseID by an innactive relationship that has to remain inactive.

I need to slice a chart with data from [Payments] by "CaseType" which is a cloumn in [Cases].

I know that I can create a calculate column in [Payments] and populate the "CaseType" using a LOOKUPVALUE. 

Is there another way in order to avoid duplicate information in the data model?

Thank you in advance!

[AlB · Accepted solution]

@setis

How about you create a second calendar table, copy of the one you have, 

Calendar2 = Calendar

and create an active relationship between Calendar2[Date] and Payments[Incident date]? Then you work with that Calendar2 in this particular dashboard. Make sure there are no filters on the original 'Calendar' on that dashboard.

Otherwise you'll have to create specific measures including USERELATIONSHIP( ) for all cases.  

[AlB]

Hi @setis

If that relationship has to remain inactive, you can probably use a virtual relationship. Have a look at this and/or this. 

I'm not sure that would be preferable to creating the additional column as you suggested, though. It'd take up less space but it would require more computing power. 

Are you sure that relationship cannot be activated?

In any case, if you show the structure of your tables (in text-tabular format) and explain what exactly you to do in that chart, we can look into it further.

Cheers

[setis]

Dear @AlB

Thanks for reaching out. I 've build the attached pbx as an example of what I'm trying to achieve. 

I have 2 issues. 

1. I have a calendar table linked on 2 data points on the table payments. I would like to be able to slice through the innactive one for this particular dashboard. I cannot change the other one to innactive as I am using that relationship for the rest of the report. 

In this case I would like to a slicer using the "Incident Date"

2. [Payments] and [Cases] are joined through an innactive relationship and I can't change this on my real report. I would like to slice a table of [Payments] using [CaseType]

Please find the file example here: https://drive.google.com/file/d/182y6OUYqWgF24Zv9fXOJgf1hfmDgkAah/view?usp=sharing

Thank you very much in advance

[AlB]

Hi @setis

I'm not sure I understand completely what you are trying to do. I mean I don't get what purpose it would serve to filter the table visual like that but well... I'm quite tired and slow today

You can create a measure like this:

FilterMeasure = IF(SELECTEDVALUE(Payments[Ref]) IN DISTINCT('Cases'[Ref]);1)

and place it in the visual level filters of the table visual you have in the report.  Then select to 'Show items when value is -->1'

The slicers on 'Cases'[Incident Date] and 'Cases'[Case Type] will then have an effect on the table visual. You'd need to have Payments[Ref] in the rows of the table visual for this to work properly.

If you want to use measures that leverage the inactive relationship between Calendar and payments you'd probably want to use USERELATIONSHIP( ) but like I said earlier we would need to know the exact details to know how to approach it.    

[setis]

Hi @AlB,

The measure works perfect. Thanks!

Apologies if I didn't explain the calendar issue properly. 

As you can see here: 

The active relationship "Date"-"Posting date" is the one that I'm using in the rest of the report. However, the table payments captures another data point that tell us when the incident that we are paying for, occurred. 

I need to be able to use a slicer to filter all the visuals on a page related to the table Payments but using the innactive relationship "Date"-"Incident Date". Is there a way to do it whithout changing the status of these 2 relationships?

[AlB · Accepted solution]

@setis

How about you create a second calendar table, copy of the one you have, 

Calendar2 = Calendar

and create an active relationship between Calendar2[Date] and Payments[Incident date]? Then you work with that Calendar2 in this particular dashboard. Make sure there are no filters on the original 'Calendar' on that dashboard.

Otherwise you'll have to create specific measures including USERELATIONSHIP( ) for all cases.