Forum Discussion

Ray_Brosius's avatar
Ray_Brosius
Helper III
5 years ago
Solved

Creating Table Visual with multiple tables

I am trying to create a simple simple table that has some of the Attributes from the CC Defects table and then the corresponding tables which hold one or more records per defect.   When I create the ...
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi Ray_Brosius ,

     

    In your scenario, you can create your relationships like below. 

     

    But, also need to create a measure like yours.

    It should be noted that "both" direction has a significant drawback about performance. You can find details here.  And, sometimes, it may cause an ambiguous model. You can find details here.

    So, I use "CROSSFILTER" function.

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

Ray_Brosius's avatar
Ray_Brosius
Helper III
5 years ago

I was actually able to solve this with a Measures

Customers = CONCATENATEX('Defects to Customer','Defects to Customer'[Customer],", ")
Components = CONCATENATEX('Defects to Components','Defects to Components'[Component],",")
Using these measures I can produce a table that shows the Defect and then in each column using the above measures a comma seperated list of the values associated with each Defect..
 
 
  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Ray_Brosius ,

     

    My previous explanation is indeed a bit problematic, here I will explain in detail.

     

    First, the two measures seem to solve the problem, but in fact, it does not. For example,

     

    The marked row is wrong. This is casued by the single direction relationship. The direction of the relationship determines the direction of filtering. So, 'CC Defects'[Defect] can filter 'Defects to Afffect'[Defect], but the reverse is not possible. The same principle applies to other relationships.

     

    So, you can find that you two measures are justed filtered by 'CC Defects'[Defect], not by Defects to Afffect'[Version]. You need to change the relationship direction from "single" to "both". You can find more details here.

     

    However, "both" direction sometimes fails. So, it is suggested to use "CROSSFILTER" function in DAX expression like below.

    Customers 2 = 
CALCULATE(
    CONCATENATEX('Defects to Customer','Defects to Customer'[Customer],", "),
    CROSSFILTER('CC Defects'[Defect],'Defects to Afffect'[Defect],Both),
    CROSSFILTER('CC Defects'[Defect],'Defects to Component'[Defect],Both)
    )

     

    Compare the result and you can find this one is right.

     

    BTW, .pbix file attached.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.