Yet Another grouping question

[jtcm4568]

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?

[sevenhills · Accepted solution]

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.

[jtcm4568]

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!

[jtcm4568]

Actually....this might work. I'm trying doing a Merge Query to get everything into one table, then applying this. I'll let you know.

[jtcm4568]

This worked with a merged query!

[rsbin]

@jtcm4568 ,

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,

[jdbuchanan71]

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