Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  Count occurrences over 2 columns and allow filter

    Posted Jan 17, 2022 10:43 AM
    I'm fairly new to PowerBI so bear with me please.

    I have a data table and trying to summarise it so it counts the occurrence of the users name in primary and secondary column producing total occurrences and by year. Could anyone offer advice on how best to achieve please?

    Table: Raw Data
    Country Year Primary Secondary
    France 2020/21 George Fiona
    Spain 2020/21 Fiona  
    UK 2020/21 Harry Fred
    France 2021/22 Fred Harry
    Spain 2021/22 Harry  
    UK 2021/22 Fred George
    France 2022/21 Harry  
    Spain 2022/21 George  
    UK 2022/21 George  

    This is what I'm trying to get to:

    Table: Yearly summary
    Name 2020/21 2021/22 2022/21 Total
    Harry 1 2 1 4
    Fred 1 2 0 3
    George 1 1 2 4
    Fiona 2 0 0 2
    Total 5 5 3 13


    ------------------------------
    James Lamb
    ------------------------------


  • 2.  RE: Count occurrences over 2 columns and allow filter

    Posted Jan 18, 2022 02:51 AM
    1. Transform Data
      1. Select the [Primary] and [Secondary] columns.
      2. Transform → Unpivot Columns.
      3. Rename [Value] to [Name].
    2. Create a measure [Count] that counts the rows in the table.
    3. Place a Matrix visual on the report, with the following field configs:
      • Rows: [Name]
      • Columns: [Year]
      • Values: [Count]
    4. Make stylistic tweaks.


    ------------------------------
    Lan Huynh
    Data Visualisation Developer
    ------------------------------

    Original Message


  • 3.  RE: Count occurrences over 2 columns and allow filter

    Posted Jan 18, 2022 03:40 AM
    @Lan Huynh Thanks for the above. The count has worked however when I add the Year to the column the count row doesn't change to show the count each year just keeps the total figure​

    ------------------------------
    James Lamb
    ------------------------------

    Original Message


  • 4.  RE: Count occurrences over 2 columns and allow filter

    Silver Contributor
    Posted Jan 19, 2022 10:18 AM
    Without seeing the report file, guessing you need a proper date dimension to get the visual to work. Check out this article and if that still doesn't work, share more about the report and we can help. https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables

    ------------------------------
    Mike Kromminga
    Data Architect
    ------------------------------

    Original Message


  • 5.  RE: Count occurrences over 2 columns and allow filter

    Posted Jan 19, 2022 07:17 PM
    IV. Add a group by advanced Name,Year, CountRows in PowerQuery

    Create a measure to sum the counted rows

    ------------------------------
    Peter Heller
    Adjunct Lecturer
    Fresh Meadows NY
    7188129488
    ------------------------------

    Original Message


  • 6.  RE: Count occurrences over 2 columns and allow filter

    Posted Jan 22, 2022 04:35 PM
      |   view attached
    Veja se o anexo ajuda

    ------------------------------
    Vilmar Santos
    ------------------------------

    Attachment(s)

    pbix
    Teste2.pbix   29 KB 1 version
    Original Message


  • 7.  RE: Count occurrences over 2 columns and allow filter

    Silver Contributor
    Posted Jan 30, 2022 04:36 AM

    Hi James

    Hope this helps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEtOVdJRMjIwMtA3MgSy3FPzi9JBQm6Z+XmJSrE60UrBBYmZeSiKIHI6Sgpg+VBvFEmPxKKiSpCiotQUsDyyLYb6RkYwOZhSNDugSmDGoNqBqh/qWHRLjFAdooBugxGaV1GtwCIbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Year = _t, Primary = _t, Secondary = _t]),

    DataType = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Year", type text}, {"Primary", type text}, {"Secondary", type text}}),

    Unpivoted = Table.UnpivotOtherColumns(DataType, {"Country", "Year"}, "Attribute","Name"),

    FilteredBlankRows = Table.SelectRows(Unpivoted, each ([Name] <> " ")),

    GroupedRows = Table.Group(FilteredBlankRows, {"Name", "Year", "Country"}, {{"Count", each Table.RowCount(_), Int64.Type}})
    in
    GroupedRows






    ------------------------------
    Naveen Ajmera
    PowerBiinDubai.com
    Dubai
    ------------------------------

    Original Message