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
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?
Solved! Go to 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.
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!
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,
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"
)
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
92 |