Getting MIN from related table

[Anonymous]

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

[Tahreem24]

@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]

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]

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. 

[Anonymous]

@Anonymous Thank you for your answer.

It's an standard report which customer use and I can't add filter or slicer, it should be a measure. Using a measure as a filter in report would affect the other measure I've used and would show incorrect value.

[Anonymous]

Hi @Anonymous 

Could you share a sample with me by your Onedrive for Business?

This 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. 

[Anonymous]

Thank you @Anonymous I could somehow resolve the issue by changing SQL view which tabular reading from.

[Anonymous]

Hi @Anonymous 

I am so glad that your problem could be solved.

Could you kindly Accept the helpful reply as the solution? More people will benefit from it. 

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. 

[Anonymous]

@Anonymous Thanks for your reply but as I wrote using the measure as filter in report will affect other measures which is used in report and I had to change the sql view on DB side. In your example there isn't any other measure related to dim1[delivered] and I'm sure that you can't get this answer without any affect on other measures in your report.