locked
DISTINCTCOUNT from more sales table RRS feed

  • Question

  • Hi, 

    I have a problem to create a measure.

    I have 3 different sales table includes the sales result per day per site per product. the first includes 2017 jan-marc, second 2017 april-aug, the last one.2017 sept-oct

    I would like to know that how many stations the proct are sold, but if I use DISTINCTOUNT(SALES_2017_1(SITE CODE)+DISTINCTOUNT(SALES_2017_2(SITE CODE)DISTINCTOUNT(SALES_2017_3(SITE CODE), the result is more (for example filtered one product for 1 stations the reuslt should be 1, but I see 3, cause the data comes from 3 differents table.

    how can I create this measure?  Thanks

    Thursday, March 22, 2018 12:32 PM

Answers

  • If the only difference between these 3 tables is the date ranges you should just merge them all into one table in your model. You could do this using the union operation in Power Query.

    http://darren.gosbell.com - please mark correct answers

    Thursday, March 22, 2018 9:31 PM
  • Are those three tables are in the database? If yes, create a view to merge them together. Then you can create a query based on that view.

    A Fan of SSIS, SSRS and SSAS

    Thursday, March 22, 2018 9:43 PM
    Answerer
  • So you could do something like the following, but it would not be nearly as fast or efficient as having a single table

    COUNTROWS( DISTINCT( UNION( VALUES( SALES_2017_1[SITE CODE] ),  VALUES( SALES_2017_2[SITE CODE] ), VALUES( SALES_2017_3[SITE CODE] ) ) ) )


    http://darren.gosbell.com - please mark correct answers

    Friday, March 23, 2018 6:27 AM

All replies

  • If the only difference between these 3 tables is the date ranges you should just merge them all into one table in your model. You could do this using the union operation in Power Query.

    http://darren.gosbell.com - please mark correct answers

    Thursday, March 22, 2018 9:31 PM
  • Are those three tables are in the database? If yes, create a view to merge them together. Then you can create a query based on that view.

    A Fan of SSIS, SSRS and SSAS

    Thursday, March 22, 2018 9:43 PM
    Answerer
  • Hi Andrea_Bacs, 

    Thanks for your question.

    Darren and Guoxiong have proposed a good solution for your. If you want to filter with product,site or date, you might need to change a little for your DAX formula based on the merged sales table. See below sample DAX formula:
    =
    CALCULATE (
        DISTINCTCOUNT ( 'SALES_Merged'[SITE CODE] ),
        ALLSELECTED ( 'SALES_Merged' )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 23, 2018 1:25 AM
  • Thanks the answer, 

    Th eproblem that is too big, so 1-1 table now includes 8m rows separatly and we split them to make easier the refresh: (so when I refresh the sales able not need to import all the 24-25 m Rows, only the last some months.

    i can use that method after that? (i dhavent use really the Power Query:-()

    No chance some distincount (union (distincount  function? or countrows?

    Friday, March 23, 2018 6:11 AM
  • So you could do something like the following, but it would not be nearly as fast or efficient as having a single table

    COUNTROWS( DISTINCT( UNION( VALUES( SALES_2017_1[SITE CODE] ),  VALUES( SALES_2017_2[SITE CODE] ), VALUES( SALES_2017_3[SITE CODE] ) ) ) )


    http://darren.gosbell.com - please mark correct answers

    Friday, March 23, 2018 6:27 AM
  • Hi Andrea_Bacs,

    Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, April 5, 2018 8:07 AM