Forum Discussion

jbobeck's avatar
jbobeck
Frequent Visitor
1 year ago
Solved

Counting records based on two dates, second count must exclude records from the first

Hi Folks,

 

I have two tables, a date dimension and a fact.  The fact table has two date columns, the date a record was added and a date if/when it was edited.  What I need to accomplish is generating a count for the number of records added and number of records edited in a date range.  I can accomplish this with two relationships between the two tables and using USERELATIONSHIP() in the count.  The trick is that if a record has both the add and edit date in the range, it should only be included in the add count. This is where it falls apart on me; I can't determine how I can get the edit count to exclude records in the add count.

 

Any advice is welcome, TIA.

  • jbobeck's avatar
    jbobeck
    1 year ago

    I was able to use this as a basis and got it working:

     

    Edited Count =
    CALCULATE(
    COUNTROWS(FactTable),
    USERELATIONSHIP(DateTable[Date], FactTable[EditedDate]),
    NOT(FactTable[RecordID] IN VALUES(FactTable[RecordID]))
    )

     

    I'm not sure how this is giving me the correct answer, but it must recognize the context that the two FactTable[RecordID]s are from different sets.

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    You can use EXCEPT for that. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • jbobeck's avatar
      jbobeck
      Frequent Visitor
      1 year ago

      I'll take a look at how to use EXCEPT.

       

      The data is very simple, I have a date dimension with sequential dates from 1950 - 2050.  The fact table is only 4 columns and is laid out like below:

       

      FactIDAddDTEditDTRecordValue
      11/1/20246/15/2024ABC123
      22/16/2024 DEF456
      32/22/2024 GHI789
      44/19/20246/16/2024JKL012
      56/30/20248/1/2024MNO345
      67/2/2024 PQR678
      78/9/202410/31/2024STU901
      810/31/2024 VWX234
      911/1/2024 YZZ567
      1012/10/2024 AAA890
  • govind_021's avatar
    govind_021
    Icon for Super User rankSuper User
    1 year ago

    Hey , use this Approach

    Edited Count =
    CALCULATE(
    COUNTROWS(FactTable),
    USERELATIONSHIP(DateTable[Date], FactTable[EditedDate]),
    NOT(
    CONTAINS(
    VALUES(FactTable[RecordID]),
    FactTable[RecordID],
    CALCULATETABLE(
    VALUES(FactTable[RecordID]),
    USERELATIONSHIP(DateTable[Date], FactTable[AddedDate])
    )
    )
    )
    )

    • jbobeck's avatar
      jbobeck
      Frequent Visitor
      1 year ago

      I was able to use this as a basis and got it working:

       

      Edited Count =
      CALCULATE(
      COUNTROWS(FactTable),
      USERELATIONSHIP(DateTable[Date], FactTable[EditedDate]),
      NOT(FactTable[RecordID] IN VALUES(FactTable[RecordID]))
      )

       

      I'm not sure how this is giving me the correct answer, but it must recognize the context that the two FactTable[RecordID]s are from different sets.

    • jbobeck's avatar
      jbobeck
      Frequent Visitor
      1 year ago

      Thanks for the reply!

       

      This is giving an error: A function "CONTAINS" has been used in a True/False expressions that is used as a table filter expression.

       

      CONTAINS and CALCULATETABLE are new to me, I'll do some exploration with them and see if I can get the error corrected.