Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
TCavins
Helper V
Helper V

DISINTCTCOUNT of column having 3 more instances in table

I have a table, lets call it TableA. Columns include (Company, FiscalYear, Quarter, ID, and some other columns).

I have chart that is showing Company, Fiscal Year and Quater.

I want to include on the chart the distinct count of IDs that are in TableA 3 or more times for the same Company, Fiscal Year and Quarter. I've tried using a calculated table to get total count but then my measures seem to be off when validating the counts. I would prefer a way to not have to create a calculated table.

Any help is appreciated!

1 ACCEPTED SOLUTION

The FILTER in the CALCULATE .. COUNTROWS is overriding part of the row context provided by ADDCOLUMNS .. SUMMARIZE. Replace that line with

"@numrows", CALCULATE(COUNTROWS(SampleTable), KEEPFILTERS( FILTER('SampleTable', SampleTable[Value5] = "Filter 1")))

and it works correctly

View solution in original post

8 REPLIES 8
johnt75
Super User
Super User

You could try

Multiple entries =
VAR summaryTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'TableA',
            'TableA'[Company],
            'TableA'[Fiscal Year],
            'TableA'[Quarter],
            'TableA'[ID]
        ),
        "@num rows", CALCULATE ( COUNTROWS ( 'TableA' ) )
    )
RETURN
    COUNTROWS ( FILTER ( summaryTable, [@num rows] >= 3 ) )

Here's an update with sample code to create a table. Value1 is ID, 2 is Company Name, 3 is FiscalYear, 4 is the quarter and 5 is the column to filter by (I'm testing by checking for "Filter 1").

SampleTable = {
    (1,"Company A",2022,2,"Filter 1"),
    (1,"Company A",2022,2,"Filter 1"),
    (1, "Company A",2022,2,"Filter 1"),
    (1,"Company A",2022,2,"Filter 11"),
    (1,"Company A",2022,2,"Filter 11"),
    (1, "Company A",2022,2,"Filter 11"),
    (1, "Company B",2022,2,"Filter 1"),
    (1, "Company A",2022,3,"Filter 1"),
    (2,"Company C",2022,2,"Filter 1"),
    (2,"Company A",2022,2,"Filter 1"),
    (2, "Company C",2022,2,"Filter 1"),
    (2, "Company C",2022,2,"Filter 1"),
    (1, "Company X",2021,4,"Filter 1"),
    (1, "Company X",2021,4,"Filter 1"),
    (1, "Company X",2021,4,"Filter 1"),
    (3,"Company A",2022,2,"Filter 1"),
    (3,"Company A",2022,2,"Filter 1"),
    (3, "Company A",2022,2,"Filter 1")
}


Here's the measure that I'm trying:

TestMeasure = 
var summaryTable = 
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER('SampleTable', SampleTable[Value5] = "Filter 1"),
            SampleTable[Value1],
            SampleTable[Value2],
            SampleTable[Value3],
            SampleTable[Value4]
        ),
        "@numrows", CALCULATE(COUNTROWS(SampleTable), FILTER('SampleTable', SampleTable[Value5] = "Filter 1"))
    )
    RETURN COUNTROWS(FILTER(summaryTable, [@numrows] >= 3))

Based on the data I put in, Company A for FY22 and Q2 should have 2. My measure is showing 3.

TCavins_0-1657645434435.png

 

It appears to be ignoring the @numrows >= 3. If I edit the table to change the company names for ID 2 and company name, I get the correct value for TestMeasure.

The FILTER in the CALCULATE .. COUNTROWS is overriding part of the row context provided by ADDCOLUMNS .. SUMMARIZE. Replace that line with

"@numrows", CALCULATE(COUNTROWS(SampleTable), KEEPFILTERS( FILTER('SampleTable', SampleTable[Value5] = "Filter 1")))

and it works correctly

John,

 

My apologies. This is the correct solution. The data that we were using had a disconnect with the calendar table and the FY/Qtr that was directly in the table. Based off of the calendar, there are only 22 results, but going off the FY/Qtr in the table there are 23. Found it out after playing with filters without using the calendar table.

This table works. The only change is the row in red where the company name is now Company xA.

SampleTable = {
(1,"Company A",2022,2,"Filter 1"),
(1,"Company A",2022,2,"Filter 1"),
(1, "Company A",2022,2,"Filter 1"),
(1,"Company A",2022,2,"Filter 11"),
(1,"Company A",2022,2,"Filter 11"),
(1, "Company A",2022,2,"Filter 11"),
(1, "Company B",2022,2,"Filter 1"),
(1, "Company A",2022,3,"Filter 1"),
(2,"Company C",2022,2,"Filter 1"),
(2,"Company xA",2022,2,"Filter 1"),
(2, "Company C",2022,2,"Filter 1"),
(2, "Company C",2022,2,"Filter 1"),
(1, "Company X",2021,4,"Filter 1"),
(1, "Company X",2021,4,"Filter 1"),
(1, "Company X",2021,4,"Filter 1"),
(3,"Company A",2022,2,"Filter 1"),
(3,"Company A",2022,2,"Filter 1"),
(3, "Company A",2022,2,"Filter 1")
}

Thanks for the reply, but the numbers still don't match what they are when I filter the column in the Data tab for the table. They looks closer to the values from what I had previously but still not right.

var summaryTable =
ADDCOLUMNS(
SUMMARIZE(
FILTER('TableA', 'TableA'[OtherColumn] = "Filtered Value"),
'TableA'[ID],
'TableA'[CompanyName],
'TableA'[fiscal_year],
'TableA'[quarter]
),
"@numrows", CALCULATE(COUNTROWS('TableA'))
)
RETURN COUNTROWS(FILTER(summaryTable, [@numrows] >= 3))

I think you need to pass the other column filter into the CALCULATE(COUNTROWS()) as well

John, Thanks. 

This helped for the most part but I have some totals that are off by 1. I can see a company with 23 people that have 3 or more records but the measure is showing 22. Other companies, the measure shows the correct value. I verified the data and it is 23 for the issue I'm looking at.

Here is my updated measure:

People 3 or More = 

var summaryTable = 
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER('TableA', 'TableA'[FilteredColumn] = "Some Filter" && 'TableA'[fiscal_year] = SELECTEDVALUE('Calendar'[fiscal_year]) && 'TableA'[quarter] = SELECTEDVALUE('Calendar'[quarter])  ),
            'TableA'[ID],
            'TableA'[Company Name],
            'TableA'[fiscal_year],
            'TableA'[quarter]
        ),
        "@numrows", CALCULATE(COUNTROWS('TableA'), 'TableA'[FilteredColumn] = "Some Filter" && 'TableA'[fiscal_year] = SELECTEDVALUE('Calendar'[fiscal_year]) && 'TableA'[quarter] = SELECTEDVALUE('Calendar'[quarter]) )
    )
    RETURN COUNTROWS(FILTER(summaryTable, [@numrows] >= 3))

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.