Forum Discussion
Resolver IIDistinctCount, Count or Countrows with filters
Hi All,
I am trying to create a visual that identifies the number of Company IDs that have a row for Technology of Tech1 but also has a row for Product = P2*. I've tried using distinct count, count and countrows as a measure and it's just coming up blank. Do I need to create a separate table of one of these columns and join it to the original table to get what I'm looking for?
Sample Data
CompanyID Type Category Product Technology
155 1 A P1 Tech1
155 1 B P2Y Tech2
158 1 A P1 Tech1
158 1 C P2X Tech2
160 1 A P1 Tech1
160 1 C P2Y Tech2
165 1 C P3 Tech4
165 1 B P4 Tech1
168 1 C P9 Tech1
Desired Outcome:
5 Distinct CompanyIDs with Tech1
And of those same CompanyIDs, 3 have a Product = P2* or 60%
Measure = CALCULATE (
COUNTROWS( Table1),FILTER(Table1, Table1[TECHNOLOGY] = "Tech1"),
FILTER(Table1, Table1[PRODUCT] = "P2Y" || Table1[PRODUCT] = "P2X"|| Table1[PRODUCT]="P2"||Table1[PRODUCT]= "P2WConnect"||Table1[PRODUCT]= "P2Q"|| Table1[PRODUCT]= "P2E"||Table1[PRODUCT]= "P2R"||Table1[PRODUCT]= "P2T"||Table1[PRODUCT]= "P2C||Table1[PRODUCT]="P2U"||Table1[PRODUCT]= "P2R"||Table1[PRODUCT]="P2G"||Table1[PRODUCT]="P2B"))
I was able to get what I was looking for. Probably not via the most efficient method, but it worked. I did create the two tables
1. Created two new tables: Table1 with columns of companyID and technology of Tech1 and Table 2 with columns of companyID and Product = P2*
2. Created a new column that identifies if the companyID from Table2 exists on Table1
CA_Exists = IF ( Table1[companyID] IN DISTINCT ( Table2[companyID] ), 1, 0 )
3. Created new Measure that calculates a distinct count of the Table1[companyID] with a filter.
CA_Total = Calculate(DISTINCTCOUNT(Table1[companyID]), FILTER(Table1,Hospital_IPEHR[CA_Exists] = "1"))
4. Created a Measure displayed in a Card that looks at the percentage of companyID
Tech1 with ProductP2*= DIVIDE(Table1[CA_Total],DISTINCTCOUNT(Table1[companyID]))
5 Replies
- amitchandak
Super User
ce138867 , You can try the search. But in your sample In do not see P2* in Tech1 and it has and with that
calculate(COUNTROWS( Table1),FILTER(Table1, Table1[TECHNOLOGY] = "Tech1" && search("P2", Table1[PRODUCT],,0)>0 ))
or
calculate(COUNTROWS( Table1),FILTER(Table1, Table1[TECHNOLOGY] = "Tech1" || search("P2", Table1[PRODUCT],,0)>0 ))
or
calculate(COUNTROWS( Table1),FILTER(Table1, Table1[TECHNOLOGY] = "Tech2" && search("P2", Table1[PRODUCT],,0)>0 ))
refer search : https://youtu.be/mZt0HJw4gjQ
containsstring: https://youtu.be/XbgLGDvWdWQ
Does the above reply helps. if you need more help make me @
Appreciate your Kudos.
ce138867Hi amitchandak, thank you for responding and your suggestions!
The * in P2* is what I am referencing as part of a wildcard search to search for anything that would start with P2. In the measure I listed any value that starts with P2. Any type of P2 product would not have a technology of Tech 1. It may have Tech2 or a Tech5 but not a Tech1. I tried your first suggestion and still coming up blank. The second suggestion is an OR but I want it as an AND. And the third one doesn't fit the scenario I'm trying to solve.
- amitchandak
ce138867 , to start with P2, you can use left. But of search is not giving data. means page has some filter or data has special character
calculate(COUNTROWS( Table1),FILTER(Table1, Table1[TECHNOLOGY] = "Tech1" && Left(Table1[PRODUCT],2) = "P2"))
- ce138867
amitchandak , I tried your new suggestion using the Left function and still coming up blank. Not sure what you mean by the data including special characters because yes it does but the column is set to text. I do have a page filter and even if I remove it, the value is still coming up blank. It's almost as if I need to separate these into two tables. One with the Company ID where Technology = Tech1 and another table with Company ID where Product = P2* and then somehow do a count where if table1[companyID] is on table2[product] then count. I did create the two tables, but what syntax could I use to accomplish the if statement above?
- ce138867
I was able to get what I was looking for. Probably not via the most efficient method, but it worked. I did create the two tables
1. Created two new tables: Table1 with columns of companyID and technology of Tech1 and Table 2 with columns of companyID and Product = P2*
2. Created a new column that identifies if the companyID from Table2 exists on Table1
CA_Exists = IF ( Table1[companyID] IN DISTINCT ( Table2[companyID] ), 1, 0 )
3. Created new Measure that calculates a distinct count of the Table1[companyID] with a filter.
CA_Total = Calculate(DISTINCTCOUNT(Table1[companyID]), FILTER(Table1,Hospital_IPEHR[CA_Exists] = "1"))
4. Created a Measure displayed in a Card that looks at the percentage of companyID
Tech1 with ProductP2*= DIVIDE(Table1[CA_Total],DISTINCTCOUNT(Table1[companyID]))
