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.

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.