Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
Download Source PBIX
I'm new in power BI,
I have 4 tables 1 is actual text/data, 3 are lookup tables (single word, 2 words, 3 words lookup).
I want to filter data table based on lookup table selection as shown in below image.
Here frequency is dummy, means number of occurance of lookup value in data.
Expected Output:
Thanks & Regards,
Adeel
Solved! Go to Solution.
Hi @adeelnazir786 ,
We can create a measure and use it in visual filter to meet your requirement:
VisualControl =
VAR Title =
SELECTEDVALUE ( 'data'[title], BLANK () )
RETURN
IF (
IF (
ISFILTERED ( 'Sheet1'[key] ),
CALCULATE (
COUNTROWS ( 'Sheet1' ),
FILTER ( 'Sheet1', CONTAINSSTRING ( Title, [key] ) )
) > 0,
TRUE ()
)
&& IF (
ISFILTERED ( 'Sheet2'[key] ),
CALCULATE (
COUNTROWS ( 'Sheet2' ),
FILTER ( 'Sheet2', CONTAINSSTRING ( Title, [key] ) )
) > 0,
TRUE ()
)
&& IF (
ISFILTERED ( 'Sheet3'[key] ),
CALCULATE (
COUNTROWS ( 'Sheet3' ),
FILTER ( 'Sheet3', CONTAINSSTRING ( Title, [key] ) )
) > 0,
TRUE ()
),
1,
-1
)
Best regards,
Hi @adeelnazir786 ,
@v-lid-msft , I dont know what was happened,
Actually post was editted by mobile, after that image and source file becomes raw text 🙂 .
Can you please lookinto my problem, thanks in advance.
Hi @adeelnazir786 ,
We can create a measure and use it in visual filter to meet your requirement:
VisualControl =
VAR Title =
SELECTEDVALUE ( 'data'[title], BLANK () )
RETURN
IF (
IF (
ISFILTERED ( 'Sheet1'[key] ),
CALCULATE (
COUNTROWS ( 'Sheet1' ),
FILTER ( 'Sheet1', CONTAINSSTRING ( Title, [key] ) )
) > 0,
TRUE ()
)
&& IF (
ISFILTERED ( 'Sheet2'[key] ),
CALCULATE (
COUNTROWS ( 'Sheet2' ),
FILTER ( 'Sheet2', CONTAINSSTRING ( Title, [key] ) )
) > 0,
TRUE ()
)
&& IF (
ISFILTERED ( 'Sheet3'[key] ),
CALCULATE (
COUNTROWS ( 'Sheet3' ),
FILTER ( 'Sheet3', CONTAINSSTRING ( Title, [key] ) )
) > 0,
TRUE ()
),
1,
-1
)
Best regards,
Great work! Please share dax to calculate the frequency (Existance count) of selected text (lookup text).
I've been use part of your formula which returns the number of findings, that really working 😛
CALCULATE ( COUNTROWS ( Sheet1 ), FILTER ( Sheet1, CONTAINSSTRING ( Title, [key] ) ) )
Thanks & Regards,
Adeel
Hi @adeelnazir786 ,
Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, we believe it can benefit more users. If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.
Best regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |