Forum Discussion

msprog's avatar
msprog
Advocate III
6 years ago
Solved

RELATEDTABLE and distinct

HI, I need some help here. I have a simple powerbi model with 2 tables, Product and Sales.   The Sales table has fields: OrderNo, LineNo, ProductKey , Qty, etc The Product table has ProductKey, ...
Need Help
  • vik0810's avatar
    vik0810
    6 years ago

    Hi msprog,

     

    you can use following DAX:

     

    NumberOfOrders = CALCULATE(DISTINCTCOUNT(sales[OrderNo]), RELATEDTABLE(sales))
vik0810's avatar
vik0810
Resolver V
6 years ago

Hi msprog,

 

you can use following DAX:

 

NumberOfOrders = CALCULATE(DISTINCTCOUNT(sales[OrderNo]), RELATEDTABLE(sales))
  • msprog's avatar
    msprog
    Advocate III
    6 years ago

    Hi @vik0810

    I have a followup question on this if it is ok. 

     

    NumberOfOrders = CALCULATE(DISTINCTCOUNT(sales[OrderNo]), RELATEDTABLE(sales))

    The solution works fine but as i am reading more about PowerBI , a question arose  on the use of RelatedTable function in this solution, which i am hoping the  you/community can help clarify.  I am wondering why the below code is not working:

     

    NumberOfOrders = CALCULATE(DISTINCTCOUNT(sales[OrderNo]), sales)

     

    why do we need the RelatedTable when we are using Calculate?

     

    THis is my thinking:

    I read that the Calculate function converts Row into a filter context and the filter context filters the whole model based on the realtionships.  So we have a 1 to many relationship between Product and Sales.  we are creating a calculated column in Product.  So there is a row context.  Now the use of Calculate should convert the row context into filter, which means the Sales table should get filtered, automatically?  Please help clarify

     

    thanks