Forum Discussion

FU's avatar
FU
Helper IV
3 years ago

Counting Rows in a Matrix [unsolved 15/01]

 

Hi all,

 

I am trying to create a (card or any other) visual which counts the number of distinct rows that are displayed in the matrix shown above.

 

Please note I have the measure in the matrix filters (distinctcount) to only show me rows that have different values in each date column (hopefully clearly shown)

 

E.g. There are rows which have the same data across the dates and therefore they are hidden.

 

Does anyone know how I can count the number of distinct rows showing based on the date range selected?

 

Full data sat can be found Below

pbix file: https://we.tl/t-vd5jYugdD6

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    FU Without a sample of your data hard to be specific. But, essentially, recreate your table/matrix in a table VAR and then use COUNTROWS.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        FU Sure, that helps greatly.

        Measure = 
    VAR __Table = 
        ADDCOLUMNS(
            DISTINCT('Project Risks Query-xxx'[Risk ID]),
            "__DistinctCount", [DistinctCount]
        )
    VAR __Result = COUNTROWS(FILTER(__Table,[__DistinctCount] > 1 && [Risk ID] = "PRID001"))
RETURN
    __Result
  • FU's avatar
    FU
    Helper IV
    3 years ago

    Greg_Deckler 

    so ultimately I want to count the number of ''attribute'' rows which is connected to the ''Date created'' filter 

  • FU's avatar
    FU
    Helper IV
    3 years ago

    Greg_Deckler Greg_Deckler 
    You are correct in saying that the number of ''Attribute'' Rows may vary depending on the sliced ''date created'' filter. 

    The image below might be clearer than the one you have:


    For those particular dates shown theres only 1 row that has change therefore I want a card to show the count of rows shown between those selected dates.

    Lets assume I change another row -  Risk ID PRID002 in my Excel table (today's date 14/01) and load it into the query - assuming my date ranges are set from min and max - I should be able to see 2 rows according to the image above. 1 change in PRID001 and 1 change in PRID002. Hope im making sense 🙂

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      FU Fairly certain this is what you are looking for, PBIX is attached below signature.

      Measure = 
    VAR __Table = SUMMARIZE(FILTER('Project Risks Query-xxx',[Attribute]="Status"),[Risk ID],[Date created],"__Value",MIN([Value]))
    VAR __Table1 =
        ADDCOLUMNS(
            __Table,
            "__DistinctCount", 
                    VAR __RiskID = [Risk ID]
                    VAR __Result = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Risk ID] = __RiskID),"__Value",[__Value])))
                RETURN
                    __Result
        )
    VAR __Result = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[__DistinctCount]>1),"__RiskID",[Risk ID])))
RETURN
    __Result