Forum Discussion

gunter_gysegom's avatar
gunter_gysegom
Regular Visitor
6 years ago
Solved

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...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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's avatar
gunter_gysegom
Regular Visitor
6 years ago

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's avatar
gunter_gysegom
Regular Visitor
6 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      gunter_gysegom
      Regular Visitor
      6 years ago

      hi MFelix 

       

      It work thanks for the help