Forum Discussion

setis's avatar
setis
Icon for Post Partisan rankPost Partisan
7 years ago
Solved

Slicing through inactive relationship

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!

Need Help
  • AlB's avatar
    AlB
    7 years ago

    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.  

7 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      setis
      Icon for Post Partisan rankPost Partisan
      7 years ago

      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's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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 :smileyindifferent:

         

        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.