Dax PB BI~DateTable Single Relationship to Facts Table Including Multiple Date Columns Distinct ID

Question

Distinct Count No Blank ~ Date Table Single Relationship to Facts Table Including Multiple Date Columns

I have a Calendar Date Table for multiple tables and use it across all the slicers. I need to calculate a measure for Distinct Count ID in 2024 across all 3 Dates from the picture attached Problem

Measure Required Distinct Count No Blank if (Created or Quoted or Sold) = Calendar Date Table Relation however,

Measure Calculate(Calculate(Calculate(distinct count no blank[ID], use relationship (Created - Date Table Date)), use relationship (Quoted - Date Table Date)), use relationship (Sold - Date Table Date))

Answer 1

You'll need to "collect" the IDs for each relationship, then distinct count those. For example:

Your Measure = 
  var createdIDs = CALCULATETABLE( DISTINCT('Data'[ID]) )
  var quotedIDs = CALCULATETABLE (DISTINCT('Data'[ID]), USERELATIONSHIP('Date'[Date], 'Data'[Quoted]) )
  var soldIDs = CALCULATETABLE( DISTINCT('Data'[ID]), USERELATIONSHIP('Date'[Date], 'Data'[Sold]) )
  
  return COUNTROWS(
    DISTINCT( UNION(createdIDs, quotedIDs, soldIDs) )
  )