Forum Discussion

Jodallen123's avatar
Jodallen123
Icon for Helper I rankHelper I
1 year ago

Tricky count measure

Hi,

 

I am trying to create a measure that counts the number of IDs per attribute, only the ID with the max date in the filtered period should be counted. This measure will be used in a matrix visual with attribute in the rows with ID under, and then a date (most likely week, but might be yearmonth) of some sort in the columns. 

 

My data looks like this (notice that 102206 appears twice):

IDAttributeDaysDate
102206Step1232025-01-02
026174Step24132025-01-02
102071Step212025-01-03
103311Step232025-01-03
103513Step232025-01-03
104152Step3782025-01-02
104350Step3702025-01-02
104393Step3652025-01-02
103650Step3862025-01-02
104299Step3702025-01-02
104097Step3712025-01-02
104233Step3692025-01-02
104474Step3592025-01-02
102772Step3882025-01-03
103651Step3942025-01-03
103899Step3922025-01-03
102206Step3152025-01-04
104108Step3682025-01-02
105270Step4172025-01-02
105231Step4202025-01-02
105216Step4212025-01-02
105285Step4152025-01-02

 

And the output should be like this:

Week1
Step10
Step24
Step313
Step44
Total21

 

Above is of course only one weeks worth of data, the actual report will probably be filtered to show the weeks in the current month. 

 

I would very much appreciate if someone has some input or ideas how a measure like this can be created.

 

Have a nice day!

3 Replies

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

    Hello Jodallen123 

    Thank you for providing a sample dataset along with the results. To achieve the desired outcomes, you can create a DAX measure using the following formula:

     

    ID Count =
VAR _virtualTbl =
    ADDCOLUMNS (
        'Table',
        "@MaxDate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[ID] = EARLIER ( 'Table'[ID] )
                        && 'Table'[Date] > EARLIER ( 'Table'[Date] )
                )
            )
    )
RETURN
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( _virtualTbl, [@MaxDate] = BLANK () || [Date] = [@MaxDate] )
    )

    Make sure to change Table with the actual table name in your working Power BI file.

     

    Here is the screenshot of the desired result in Power BI:

     

    By default, "Step1" will not be displayed due to its blank or zero ID count. To make it visible, ensure that you check the "Show Items with no data". Below is a screenshot indicating where you can find this option:

     

     

    Click on the "Attribute" in the Rows section and then, click on "Show items with no data"

     

    I am attaching the Power BI file for your reference. Please review it and let me know if you have any further queries.

     

    Best Regards,
    Udit

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

    Proud to be a Super User

     

     

     

     

    • Jodallen123's avatar
      Jodallen123
      Icon for Helper I rankHelper I
      1 year ago

      Hi,

       

      Thanks for the help!

       

      I tried the measure that you posted on my full data-table and I could not get it to work. Then I made a copy of my main table with just one week filtered like what I posted above and then it seems to work, so it seems that something strange happens when there is more data. The output I get on my full data table looks like this: 

      Week1
      Step1 
      Step23
      Step31
      Step 44
      Total8

       

      For example for step 2, it shows 3 since there are 3 IDs on the max date, but there is also one ID that should be shown from the day before. Step 4 is correct since all IDs end up on the same day. I am unsure how step3 has 1 since there are 13 in total (in my real data set there is no entry on 2025-01-04, I just added that to show that there can be more than one ID for one period). If it followed the logic of step 2 and 3 I think step3 should show a 3 since there are 3 IDs in step3 on the max date for that attribute (2025-01-03).

       

      I would very much appreciate futher assistance!

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

        Hi Jodallen123 

        Could you please provide sample data that fully represents your issue or question in a usable format, rather than a screenshot? Ensure the dataset is anonymized and does not contain any sensitive or unrelated information.

         

        Additionally, it would be very helpful if you could share the expected outcome based on the provided data - this can be in any format, including a screenshot. Having a clear reference for comparison will significantly improve the chances of getting the correct solution in the first response.

         

        For best practices, you may find the following links useful:

         

        Thanks,
        Udit