Forum Discussion

MyWeeLola's avatar
MyWeeLola
Icon for Helper II rankHelper 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.

vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
1 year ago

Hi, MyWeeLola ,
you won't do that in DAX unless you create new physical table.

 

Here's how you can do it in PQ:
Before:

 

after:

 


The code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDJR0lEyVIrVwSJgRFCFMUEBUxSBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [attribute = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"attribute", type date}, {"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"attribute"}, {{"sum_value", each List.Sum([value]), type nullable number}})
in
    #"Grouped Rows"

group by settings:

 

 

  • MyWeeLola's avatar
    MyWeeLola
    Icon for Helper II rankHelper 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
      Icon for Super User rankSuper 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
        Icon for Helper II rankHelper 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