Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jtcm4568
Helper I
Helper I

Yet Another grouping question

Hi all!

I have some data, defined as:

Data_TypeData_QueueData_Count
ABCCar123
ABCABC_Count456
DEFTruck8
DEFDEF91
DEFDEF_Count12

 

I need to show ONLY the values for Data_Count where Data_Queue = Data_Type + '_COUNT', so like:

Data_Type

Data_Count

ABC456
DEF12

 

I am not sure how to write the filter to meet these needs. Any insight?

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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.

View solution in original post

6 REPLIES 6
sevenhills
Super User
Super User

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!

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.

This worked with a merged query!

rsbin
Super User
Super User

@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
Super User
Super User

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

jdbuchanan71_0-1672862217840.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.