Retrieve value from a column filterd on a 2ndcolumn form the sale table

[gunter_gysegom]

1.  

I have a report containing 3 tables

Table 1 Data

Table 2 Customer Data

Table 3 Dimension Data

The 3 tables containes the customer no. as a common field

Table 1 is connected to table 2

Table 2 is connected to table 3

Table 3 cannot be connected to table 1 because it a many to many relationship

In table 3 each customer has multiple dimenions values

Ex

Customer No. Dimension Type Dimension Value

0001                   Type 1                           Value 1

0001                   Type 2                           Value 2

In my Excel report for the data I cannot use a filter bases on the Dimension Table (I can add it but it has no effect)

So I was looking for a way to add a column in the customer tabel (table 2) or the data table (table 1) whit the correct Dimenion Value from type 2 for the correspondin customer no. So I need 2 filter the customer No. and Dimension Type = 2

I ve already tried solutions using calculate in combination with filter since lookupvalue cannot be used

But I cannot get it working.

I hope I can get some help here

[gunter_gysegom]

hello @MFelix 

I just noticed it is bit more complicated, since there is no direct link possible between Table 3 and table 1 there is another Table Table 4 which i s connected to Table 1. Table 4 is connected to Table 2

Indeed table 3 has a many to many relation with table 4

A view on the relations:

Table 2: Customer Data:

Table 3: Dimension Data

Table 1: Data:

Table 4:

So what I want to do is create 2 new colums in Table 4 which contain the following:

New Column 1: the value for the field KSTNDRAGER (from table 3) for the corresponding customer no

New Column 2: the value for the field KLANTGROEP (from table 3) for the corresponding customer no

I hope it is a bit clearer now

[gunter_gysegom]

I ve been experimenting and now Have the following formula

=CALCULATE (
FIRSTNONBLANK ( 'Distrac NVDefault Dimension'[Dimension Value Code]; 1 );
FILTER ( ALL ('Distrac NVDefault Dimension' ); 'Distrac NVDefault Dimension'[No_]= 'Distrac NV Customer'[No_]; filter ( all('Distrac NVDefault Dimension'); 'Distrac NVDefault Dimension'[Dimension Code]="KLANTGROEP")))

But now the issue is that Dax requires "KLANTGROEP" to be a table

2nd issue not every customer has a dimension KLANTGROEP

[MFelix · Accepted solution]

Hi @gunter_gysegom ,

Withouth making any test on your tables and calculation I have checked the problem with your code (1st issue you present) can you please try this formula:

CALCULATE (
    FIRSTNONBLANK ( 'Distrac NVDefault Dimension'[Dimension Value Code]; 1 );
    FILTER (
        ALL ( 'Distrac NVDefault Dimension' );
        'Distrac NVDefault Dimension'[No_] = 'Distrac NV Customer'[No_]
            && 'Distrac NVDefault Dimension'[Dimension Code] = "KLANTGROEP"
    )
)

[gunter_gysegom]

hi @MFelix 

It work thanks for the help

[MFelix]

Hi @gunter_gysegom ,

If table 1 is connected to table 2 and table 2 is connected to table 3 (assuming this is one to many from table 2 to the others) then table 1 and 3 are related trough table 2.

You need to make your calculation based on the table 2.

Your information about the data is very scarse can you please share some sample data of each table and expected result?