Forum Discussion

jahlum1's avatar
jahlum1
New Member
10 months 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:

Week of the Year [YRWK_created] a record was created

Count of records created during each YRWK_created [Count of TD_created]

 

How would I go about creating a calculation using these separate tables that is [Count of TD_comp] - [Count of TD_created] for each Week of the Year?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    10 months 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.

     

     

     

     

5 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    10 months 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].

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    10 months 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.

     

     

     

     

    • Bmejia's avatar
      Bmejia
      Super User
      10 months ago

      Just to add to conversation to Jihwan_Kim post.  I would probably make the Period a dynamic table the way you won't have to be entering the YRWK everytime you have a new value.  The table below would look at the max YRWK and Min YRWK from both tables to generate a list.

       

      Period =
      VAR MinYYWK = IF(Min(Completed[YYWK])>Min(Created[YYWK]),Min(Completed[YYWK]),Min(Created[YYWK]))
      VAR MaxYYWK = IF(Max(Completed[YYWK])>Max(Created[YYWK]),Max(Completed[YYWK]),Max(Created[YYWK]))
      RETURN
      GENERATESERIES(MinYYWK,MaxYYWK,1)
  • v-achippa's avatar
    v-achippa
    Community Support
    10 months ago

    Hi jahlum1,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you AmiraBedh, Jihwan_Kim and Bmejia for the prompt response. 

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Community Support
      10 months ago

      Hi @jahlum1,

       

      We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

       

      Thanks and regards,

      Anjan Kumar Chippa