topic Re: DISINTCTCOUNT of column having 3 more instances in table in DAX Commands and Tips

DISINTCTCOUNT of column having 3 more instances in table

TCavins — Tue, 12 Jul 2022 13:25:14 GMT

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!

Re: DISINTCTCOUNT of column having 3 more instances in table

johnt75 — Tue, 12 Jul 2022 14:12:28 GMT

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 ) )

Re: DISINTCTCOUNT of column having 3 more instances in table

TCavins — Tue, 12 Jul 2022 15:53:35 GMT

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))

Re: DISINTCTCOUNT of column having 3 more instances in table

johnt75 — Tue, 12 Jul 2022 16:02:14 GMT

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

Re: DISINTCTCOUNT of column having 3 more instances in table

TCavins — Tue, 12 Jul 2022 17:09:21 GMT

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.

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.

Re: DISINTCTCOUNT of column having 3 more instances in table

TCavins — Tue, 12 Jul 2022 17:15:39 GMT

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 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"),

(1, "Company X",2021,4,"Filter 1"),

(3,"Company A",2022,2,"Filter 1"),

(3, "Company A",2022,2,"Filter 1")

}

Re: DISINTCTCOUNT of column having 3 more instances in table

TCavins — Wed, 13 Jul 2022 13:07:11 GMT

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))

Re: DISINTCTCOUNT of column having 3 more instances in table

johnt75 — Wed, 13 Jul 2022 14:08:31 GMT

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

Re: DISINTCTCOUNT of column having 3 more instances in table

TCavins — Wed, 13 Jul 2022 17:11:29 GMT

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.