Forum Discussion
Yet Another grouping question
Hi all!
I have some data, defined as:
| Data_Type | Data_Queue | Data_Count |
| ABC | Car | 123 |
| ABC | ABC_Count | 456 |
| DEF | Truck | 8 |
| DEF | DEF | 91 |
| DEF | DEF_Count | 12 |
I need to show ONLY the values for Data_Count where Data_Queue = Data_Type + '_COUNT', so like:
Data_Type | Data_Count |
| ABC | 456 |
| DEF | 12 |
I am not sure how to write the filter to meet these needs. Any insight?
Add the column as
Has Word Count = IF( CONTAINSSTRING( Table_WordFilter[Data_Queue], Table_WordFilter[Data_Type] & "_Count") , 1, 0)Or
Has Word Count = IF( Table_WordFilter[Data_Queue] = Table_WordFilter[Data_Type] & "_Count" , 1, 0)Use this column as filter.
6 Replies
Try this.
Filtered Count = SUMX ( VALUES ( YourTable[Data_Type] ), VAR _Type = CALCULATE ( SELECTEDVALUE ( YourTable[Data_Type] ) ) RETURN CALCULATE ( SUM ( YourTable[Data_Count] ), YourTable[Data_Queue] = _Type & "_Count" ) )Anonymous ,
Create another calculated column:
Flag = SWITCH( TRUE(), AND( CONTAINSSTRING( [Data_Queue], "_Count" ), [Data_Type] = LEFT( [Data_Queue], 3 )), 1, 0 )Data_TypeData_QueueData_CountFlag
ABC Car 123 0 ABC ABC_Count 456 1 DEF Truck 8 0 DEF DEF 91 0 DEF DEF_Count 12 1 Then filter this new "Flag" column to show only 1's.
Hope this helps.
Regards,
Add the column as
Has Word Count = IF( CONTAINSSTRING( Table_WordFilter[Data_Queue], Table_WordFilter[Data_Type] & "_Count") , 1, 0)Or
Has Word Count = IF( Table_WordFilter[Data_Queue] = Table_WordFilter[Data_Type] & "_Count" , 1, 0)Use this column as filter.
Thank you. This is REALLY close.
What about when my two string values are in two separate data tables?
So:
INTERACTION has a column INTERACTION_TYPE which is [ABC], [DEF], etc.
RESOURCE has a column RESOURCE_TYPE which is [ABC_Count],[ABC_Type1], etc.
If I use your filter idea, I can't cross over to a different table to compare the values. I can see that they match in the resultant visualization (which is what I showed in my initial post), but the data tables don't allow the comparison.
I feel like what you suggest gets me super close, but I'm having a major challenge, for whatever reason, getting my head around how to finish it off.
Any further thoughts?
Thank you!
