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.
Dear Community,
I have this sample of raw data and a measure, for example, if I click on 'A', the table will displays 'A' if a record is found in both the seller or buyer columns.
Visual Control =
var tab = {max(Quote[Buyer]),MAX(Quote[Seller])}
var result = COUNTROWS(FILTER(DISTINCT('Name'[Buyer]),
COUNTROWS(FILTER( tab,
[Value] = 'Name'[Buyer]) ) >0 )) +0
return
if ( result>0 , 1,0)
This measure works perfectly, but when I apply it to another PBIX which have million of raw values, the loading and result will take a very long time.
However, I am having trouble transforming this measure into a column to see if it speeds up the report loading. Or is there any solution to my issues ?
Any helps would be greatly appreciated!
Pbix: https://drive.google.com/file/d/1WhqMBEx71XlTS3Njzbpb0rODfufE7IZ5/view?usp=sharing
Solved! Go to Solution.
Try this solution.
1. Create calculated column in Quote table:
Buyer-Seller = Quote[Buyer] & "-" & Quote[Seller]
2. Create calculated table:
Name =
DISTINCT (
UNION (
SELECTCOLUMNS (
Quote,
"Name", Quote[Buyer],
"Buyer-Seller", Quote[Buyer-Seller]
),
SELECTCOLUMNS (
Quote,
"Name", Quote[Seller],
"Buyer-Seller", Quote[Buyer-Seller]
)
)
)
3. Create a many-to-many relationship (Name filters Quote):
Compare the performance and let me know. There's another approach using a bridge table and bidirectional filter if performance is still an issue.
https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/
Proud to be a Super User!
Try this measure:
Visual Control =
VAR vNameSelection =
VALUES ( 'Name'[Name] )
VAR vResult =
IF (
MAX ( Quote[Buyer] )
IN vNameSelection
|| MAX ( Quote[Seller] ) IN vNameSelection,
1
)
RETURN
vResult
The Name table contains all Buyer and Seller names, is the source of the Name slicer, and has no relationship with the Quote table. Use the measure [Visual Control] as a visual filter (equals 1).
---
Proud to be a Super User!
Dear @DataInsights ,
Thanks for your attention.
Tried your measure in my PBIX (large dataset), the loading process took a long time. Is that possible to do it in calculated column?
Try this solution.
1. Create calculated column in Quote table:
Buyer-Seller = Quote[Buyer] & "-" & Quote[Seller]
2. Create calculated table:
Name =
DISTINCT (
UNION (
SELECTCOLUMNS (
Quote,
"Name", Quote[Buyer],
"Buyer-Seller", Quote[Buyer-Seller]
),
SELECTCOLUMNS (
Quote,
"Name", Quote[Seller],
"Buyer-Seller", Quote[Buyer-Seller]
)
)
)
3. Create a many-to-many relationship (Name filters Quote):
Compare the performance and let me know. There's another approach using a bridge table and bidirectional filter if performance is still an issue.
https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/
Proud to be a Super User!
Dear @DataInsights ,
Thank you! Calculated column takes less time than the measure, but since the relationship is many to many, I'm not sure if there are any issues will prompt out in the future.
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 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |