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

vojtechsima 

Thank you for the response. I am not sure what to do with that code. 

I have done very little in advanced editor. I already have a source, or is this for a brand new table, if so how do i poplulate it?

I do appreciate your assistance, and any help with this would be great.

Lola

vojtechsima's avatar
vojtechsima
Super User
1 year ago

Hello, MyWeeLola ,
well you don't have to rescue my code, you just click it, you find Group BY button and do as followed and that'S it really.

You will do the changes on your unpivoted table.

 

  • MyWeeLola's avatar
    MyWeeLola
    Helper II
    1 year ago

    vojtechsima 

    Thank you for the response. I think I see the issue.

    I mentioned i have three columns, Date, Attribute and Value. I see where you are going with the grouping. I missed a comma which probably made you think i had 2.

     

    How I do preserve the ability to search by date using your method?

     

    Lola