Creating Table Visual with multiple tables

[Ray_Brosius]

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 table with only the CC defects and one of the other three No problems. When I try to add from two of the outside tables Power BI desktop throws up an error.  The Orange highlighted fields are from the CC defect table then the "Exists In" is from one table and "Customer" is from another table in teh model. (SEE last screenshot of the model)..  If I drop either one of the last two then the table renders... ?? What am I missing?  

[Ray_Brosius]

@Icey In your "Customers 2" measure.. why do you have multiple crossfilters in the calculate function?

Ahhh wait, editing this reply..

I see that the two crossfilters go to the "other" two tables containing multiple attributes per defect.  So for each Measuer you need to cross filter against all the other tables that contain the list of attribute values per defect... 

I did not read the formula close enough on quick scan

[Ray_Brosius]

@Icey 

Thanks for the very detailed explanation.  I will test out the crossfilter...  Curiuos as to when you say sometimes the "BOTH" direction relationship fails..?  How is that and what does that mean?  I'd like to better understand this.  

[Icey · Accepted solution]

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]

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]

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.

[Icey]

Hi @Ray_Brosius ,

Normally, this is caused by returning multiple different results for the same category. Please check.

Best regards

Icey

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

[Ray_Brosius]

Hello @Icey ,

I'm not quite sure what you mean by "returning multiple different results for the same category".  In my data I have JIRA Issues that where an Issue can be tied to one or more Customers, one or more Components, and one or more Versions.  What I am trying to do is build a report that shows a list of Defects(Jira Issues) in a table and include in that table columns showing the Customers, Components and Versions for each Jira Issue.  I guess in thinking more about this the Table is confused because for a single Jira ISSUE, it does not know how to show the "list" of each potential category value?  I assumed it would just give me multiple rows per issue...   I also tried a Matrix/Pivot table to see the same.. but the Matrix table has the same behavior... 

For a scenario like this where we have an Entity that has multiple attributes, that each can contain multiple values per Item.  How do we model this properly?  Maybe I need to have on teh fact table a "column" per attribute that is a comma seperated list and use this for "display" purposes, but use the tables that I created for showing each value in each category per issue in single rows, for analysis.

[Anonymous]

Hi @Ray_Brosius - Can you share your .pbix file? (using Dropbox or google drive?)

Cheers,

-Namish B

[Ray_Brosius]

I cannot share this pbix file... customer identifying data in the dataset

but to explain the "data"

there are defects that have one or more records in teh three outside tables

This is JIRA data where the fields are mutli-select fields.  so they are broken out to handle the multiple values.

I am simply creating a table with the Defect Number, Created Date then try to add the Customer and one of the Versions.. the Table visual throws an error as soon as I select a column from the second table on the outside of the diagram...