Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Getting MIN from related table

I have a fact table with a connection to a dimension table (with key_fact and Key_dim). In the dimension table I have a column which has diffrenet delivered code from 1 to 5 and I need to write a dax code to select keys from fact table which has the minimum code; those which has delivered=1.

 

Here is how 'delivered' column looks like in dimension table:

 

 

I'm a little confused about which function should be used, lookupvalue, related,..:

 

 

delivered status:=

Var Result = calculate(
                     MIN('dim1'[delivered])
                    ,filter('fact'
                           ,RELATED('dim1'[key_dim])
                           )
                          
                       )

Return Result

 

  

Need Help
Tips and Tricks

8 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    5 years ago

    Anonymous ,

    Enhance your measure like this:

    delivered status:=

Var Result = calculate(
                     MIN('dim1'[delivered])
                    ,filter('fact'
                           ,RELATED('dim1'[key_dim]) = MIN('fact'[Key_fact])
                           )
                          
                       )

Return Result
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      key_dim and key_fact are two sequence numbers in each table and I need to select minimum on delivered column not on keys.

       

      I could resolve the issue somehow.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

        Do you want to get key_fact in Fact Table by the minimum on delivered column in DimTable?

        Due to I don't know about your data model, I build a sample to have a test.

        Dim table

        Fact:

        If your relationship is one to one try to build a slicer by delivered, when you select 1, you can get the result.

        If your relationshop is one to many, try to build a measure filter and add it into the Filter Field in the table visual.

        Set this measure to show items when value =1.

        Measure = 
VAR _MINdeliver = MINX(ALL(dim1),dim1[delivered])
VAR _Key_Dim = CALCULATETABLE(VALUES(dim1[Key_Dim]),FILTER(ALL(dim1),dim1[delivered] = _MINdeliver))
RETURN
IF(MAX('Fact'[Key_Fact]) IN _Key_Dim,1,0)

        If this reply still couldn't help you solve your problem, please show me your Fact table and your relationship.

        You can show me a screenshot of the result you want, it will make it easier for me to understand your requirement.

         

        Best Regards,

        Rico Zhou

         

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