Forum Discussion

MyWeeLola's avatar
MyWeeLola
Helper II
1 year ago
Solved

Hopefully a simple Pivot Question on counting values

Good morning   I have a table that I have unpivoted to give me 3 columns Date Attribute and value   I am trying to create a calculated column which counts the number of times the attribute appea...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi MyWeeLola 

     

    Could you tell me how you would like it sorted by date?

     

    I have some assumptions. If you want to sort attributes based on the earliest date they appear, you can create a new calculate column to query the minimum date for each Attribute:

     

     

    Start DATE = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Attribute] = EARLIER('Table'[Attribute])
    )
)

     

     

     

    Then create the new calculate column:

     

     

    MyScoreColumn = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    'Table'[Value] = 1,
    ALLEXCEPT('Table', 'Table'[Attribute])
)

     

     

    You can get:

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi MyWeeLola 

 

Thank you very much vojtechsima and Angith_Nair for your prompt reply.

 

For your question, here is the method I provided:

 

Here's some dummy data

 

 

First, filter the rows with "Value" of 1.

 

 

If you want to count the unique occurrence of each attribute on different dates, you can de-duplicate the data before grouping it.
Select the Date and Attribute columns, right-click, and select "Remove Duplicates".

 

 

Select Group By. In the dialog box that is displayed, select Attribute as the attribute column. Select "Count Rows" in "Actions"

 

 

Here is the result.

 

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

 

 

MyWeeLola's avatar
MyWeeLola
Helper II
1 year ago

Anonymous 

 

Good morning, thank you for taking the time to reply. When I do this, then I lose the ability to sort by date. Is there a solution where i can do this and sort by date?

 

Lola

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi MyWeeLola 

     

    Could you tell me how you would like it sorted by date?

     

    I have some assumptions. If you want to sort attributes based on the earliest date they appear, you can create a new calculate column to query the minimum date for each Attribute:

     

     

    Start DATE = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Attribute] = EARLIER('Table'[Attribute])
    )
)

     

     

     

    Then create the new calculate column:

     

     

    MyScoreColumn = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    'Table'[Value] = 1,
    ALLEXCEPT('Table', 'Table'[Attribute])
)

     

     

    You can get:

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.