Forum Discussion

jahlum1's avatar
jahlum1
New Member
1 year ago
Solved

Calculation using distinct counts form two different matrix tables

I have two separate matrix tables with the data below -   Matrix 1: Week of the Year [YRWK] a record was completed Count of records completed during each YRWK [Count of TD_comp]   Matrix 2: We...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
1 year ago

Hello !

Thank you for posting on Microsoft Fabric community.

You need to reate a date table with a date column and a YrWk key for exampleYYYYWW and creare 2 relationships with your fact table twice:

  • active: date[date] to fact[createddate]

  • inactive: date[date] to fact[completeddate]

TD Created =
CALCULATE(
    DISTINCTCOUNT(fact[TD #]),
    USERELATIONSHIP('date'[date], fact[createddate])
)

TD Completed =
CALCULATE(
    DISTINCTCOUNT(fact[TD #]),
    USERELATIONSHIP('date'[date], fact[completeddate])
)

TD Delta = [TD Completed] - [TD Created]

then put 'date'[YrWk] on the matrix rows and show [TD Delta].