Forum Discussion
Retrieve value from a column filterd on a 2ndcolumn form the sale table
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
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" ) )
5 Replies
- MFelix
Super User
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?
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
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
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" ) )
