topic Re: Column of related Table breaks measure in DAX Commands and Tips

Column of related Table breaks measure

LuITS — Mon, 21 Oct 2024 14:33:30 GMT

Hello,

I have two Tables (A and B) and have a meassure (using only data from A) which works fine in my table visual, but when i add a column from Table B to the visual, the meassure breaks.

SUMX(FILTER('A, 'A'[Flag]=1), 'A'[Value1] / 60 * 'A'[Value2])

I think the Problem is the many to many relatioship between A and B. For Key in A there are three rows in Table B matching B contains translations from differnt languages for that code.

When i switch the added collumn from don't ssummarize to First or Last the problem resolves.

Normaly a slicer on the page should filter unessecery translation in Tbale B, but they appear when i add the collumn to my table visual.

Sorry for poor decription. But is there a way, maybe to Filter Table B in my measure with the selection made in my slicer, so i dont need to select first to get rid of unwanted translations?

Re: Column of related Table breaks measure

Greg_Deckler — Mon, 21 Oct 2024 14:59:25 GMT

LuITS Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Column of related Table breaks measure

Rupak_bi — Mon, 21 Oct 2024 15:03:34 GMT

Hi,

You can always filter tables by slicer provided the slicer column related to both the tables. For that you may need to create a key table. The details you have provided doesn't describe about table B and the relationship you have and also the issue you are facing. Please provide these details for right solution.

Re: Column of related Table breaks measure

LuITS — Tue, 22 Oct 2024 07:01:05 GMT

Sry for poor description.

Table A:

Value1	Value2	Code	Flag	US	BR	FR
60	1000	1	1	S1us	S1br	S1br
10	600	2	0	S2us	S2br	S2br
30	500	3	1	S3us	S3br	S3br

Table B:

Code	Language	Trans
1	US	us1
1	BR	br1
1	FR	fr1
2	US	us2
2	BR	br2
2	FR	fr2
3	US	us3
3	BR	br3
3	FR	fr3

Table C:

This Table is a Field Parameter containing a Shiftmodel with the 3 languages and Translations for each Shift. Its uses 3 Collumns from Table 1 (US, BR, FR). I use this Fieldparametertable as a Slicer to slelect the language for the report, respectifly filtering another slicer containig the translated shifts. To be honest i don't know

how i managed that the first slicer shows the Column Headers (US, FR, BR) and the second slicer show the translated Shifts. But it works.

Language	Language Fields
US	'Table A'[US]
BR	'Table A'[BR]
FR	'Table A'[CS]

Slicer Example:

Language Shift

US (selected) S1us

BR (not selected) S2us

CS (not selected) S3us

Table relations:

Table A [Code] * -> * Table B[Code]

Table C [Language] 1 -> * Table B[Language]

Visual Expected Output: (Slicer Selection US and S1us)

Language	Code	Trans (Table B)	Value1	Measure
S1us	1	us1	60	1000
S1us	2	us2	10
S1us	3	us3	20	250
Total			90	1250

Visual Output:

Language	Code	Trans (Table B)	Value1	Measure
S1us	1	us1	60	1000
S1us	1	us2	60	1000
S1us	1	us3	60	1000
S1us	1	br1	60	1000
S1us	1	br2	60	1000
...	...	...	...	...
Totals			90	1250

The Table gets extrem long. It seems like that every Code in my Visualtable has a row for every Translation (all languages). When i slecect Select First and disble don't summarize vor Column Trans I get the expected results.

I dont rellay know what to do. Maybe you have a better solution for my Translationproblem i try to resolve with the field parameters which can help, or if i need a filter in my measure which filters the output by the selected language and shift.

Re: Column of related Table breaks measure

Anonymous — Wed, 23 Oct 2024 06:51:53 GMT

Thanks for the replies from Greg_Deckler and Rupak_bi.

Hi LuITS ,

Based on your description above, I have created the data and realized your requirement by changing the relationship between Table A and Table B. Here's what I did:

1.Create a new table:

2.Create the following relationship:

3.Result:

Best Regards,
Zhu

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

Re: Column of related Table breaks measure

LuITS — Wed, 23 Oct 2024 10:04:06 GMT

Hi, thanks for your help.

I had a look at your file but its not quite the right outcome i want.

When selecting a language, i get the right shifts in the slicer and the right shift traslation in the table visual.

But i also get a row for every translation of event code. When selected FR, i only want to have the FR translations of EventCode.

When I look at the Data Table Outcome should have 3 rows.

I treid a relationship between Language Table and Translation Table directly, but i cant activate it. I get Error: you cannot create a direct relationship between Translation and Data Table, as this would lead to ambiguity between Tables Language and Translations.

Can you help me with thi issue too?

Re: Column of related Table breaks measure

Anonymous — Fri, 25 Oct 2024 06:46:46 GMT

Hi LuITS ,

Could you please upload your file to a cloud storage (like OneDrive) and paste the link to that storage location in the post? This will help your problem to be solved in the best possible way.

For the second question, you can try checking the existing relationship to set it to inactive status.

Best Regards,
Zhu

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