Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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!
Solved! Go to 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
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.
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.
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.
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))
User | Count |
---|---|
12 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
27 | |
19 | |
13 | |
11 | |
7 |