Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
4 years ago
Solved

distinctcount with crossfilter?

Hello all
I would like to have just one measure to count all distinct contractID.
Here is my model

My main table, documents, is connected with dates with an active connection. My other table, contracts, has a dates connection but it is inactive. I would like to count all distinct contractID, from entities, from several columns filters from documents, as well as from contracts and also, by seller type, from contractseller. This last table is connected to contracts with a bi directional connection. 
My desire visual would be, filtered by month, year and seller type. On the context visual i would like to see all contracts by seller name, month and status doc (from document table). Like this:


Thank you all so much

Need Help DAX
  • amitchandak's avatar
    amitchandak
    4 years ago

    pedroccamaraDBI , if contract and document are two fact, they should join. Or should be merged into one fact.

     

    even if the join is inactive you can use these dates filter like

     

    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = minx(allselected(Date1),Date1[Date])
    return
    calculate( distinctCOUNT(Contrcat[Contract ID]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

     

    if need add cross join to remove date join with document

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    pedroccamaraDBI , if contract and document are two fact, they should join. Or should be merged into one fact.

     

    even if the join is inactive you can use these dates filter like

     

    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = minx(allselected(Date1),Date1[Date])
    return
    calculate( distinctCOUNT(Contrcat[Contract ID]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

     

    if need add cross join to remove date join with document

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi pedroccamaraDBI 
    I don't see any reason not to use

    Contract ID Count =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( Contract[ContractID] ),
    USERELATIONSHIP ( 'Dates'[Date], Contract[StartDate] )
)

    Please let me know if this answers your query.