Counting Rows in a Matrix [unsolved 15/01]

[FU]

 

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

[FU]

@Greg_Deckler still unsolved! Counting on you 🙂

[Greg_Deckler]

@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

[FU]

@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 🙂

[FU]

@Greg_Deckler 

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

[Greg_Deckler]

@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.

[FU]

@Greg_Deckler 

Would you be able to show me what you mean? I have attached my pbix file here: https://we.tl/t-vd5jYugdD6

[Greg_Deckler]

@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]

@Greg_Deckler 

What can I do with this measure? Note that my table consists of many rows, I've currently got a measure in to show me the rows that have different values in the date column so essentially I'm tracking data changes over time of the exact same table.

I want to create a card or something which counts the number of rows based on the date created slicer that's shown. 

Would be helpful if you could show an example on the pbix file and send it over 🙂

[Greg_Deckler]

@FU I used your PBIX when I created that measure. So you can just copy and paste it into your PBIX. I'm not quite certain what you are going for. Your matrix has some filters on it, do you want those same filters included in your count or not? 

[FU]

@Greg_Deckler 

So let me explain:

I have imported the table "project risks" from an excel file on those different days shown in the filter so that I could see the changes in my values over time. 

1) want to select a range from the date slicer and see what rows have changed from each table.

2) I want to create a card which counts those rows.

E.g. If I select 22/12- 24/12 and I have 3 rows that have changes between those dates then I want a measure which counts those 3 rows and displayed in a card. 

So ultimately a measure which dynamically counts the number of rows shown in the matrix when filtered by "date created" 

[Greg_Deckler]

@FU OK, so that I am absolutely clear. You may or may not have more than 3 rows in the matrix is that correct? But, you only want to count the rows where the status has changed between any dates, right? Or, do you want to count only if the min and max dates status is different?