Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help in DAX, Distinct Count

Hi, I am trying to calculate distinct count for Supplier ID based on Member ID, but it's not working the I want, please find DAX below DistinctSupplierID = var __member = T[MemberID] var __endDat...
DAX
DAX Calculation
distinctcount
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous , OK, you want this:

     

    Column = 
var __endDate = T[ReportReceived].[Date]
var __startDate = EDATE( __endDate, -6)
RETURN
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(
                    'T',
                    [MemberID] = EARLIER([MemberID]) &&
                        T[ReportReceived].[Date] <= __endDate &&
                            T[ReportReceived].[Date] >= __startDate
                ),
                "SupplierID",[SupplierID]
            )
        )
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Perhaps:

 

Column = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('T',[MemberID] = EARLIER([MemberID])),"SupplierID",[SupplierID])))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    The result is 6. I am not expecting this. I want from ant date to last 6 months how many unique Supplier Id. 

    For above data the distinct supplier Id column should be 

    2,2,4,3,2,2,5,3,5,5,5,3,3,3,2,3,1,4,2

    Greg_Deckler 

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Oh, you commented out that line in your code so I thought you didn't want it. It is hard for me to test exactly because I'm in the US so your dates don't convert so well, let me see what I can do about that.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous , OK, you want this:

       

      Column = 
var __endDate = T[ReportReceived].[Date]
var __startDate = EDATE( __endDate, -6)
RETURN
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(
                    'T',
                    [MemberID] = EARLIER([MemberID]) &&
                        T[ReportReceived].[Date] <= __endDate &&
                            T[ReportReceived].[Date] >= __startDate
                ),
                "SupplierID",[SupplierID]
            )
        )
    )