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.
I would like to filter results to show all locations with Product Line A and show all of the other Product Lines produced in that location.
This is the bad result
I would like to see the other Product Lines made in those locations at the same time.
This is the Measure that I use to generate the "List of Product Line values" column
Main table of values is 'allChart'
Let me know if you have any ideas.
Thank you,
-Brett
Solved! Go to Solution.
Hi @Brett007
The solution I would suggest involves using ALL ( 'allChart'[Product Line] ) to clear the filter on Product Line when determining the list of Product Line values
In the code below, ValuesComplete is a list of Product Lines with the Product Line filter cleared.
I also rewrote the code slightly, using the fact to TOPN will return all the values if there are fewer than N.
Does this work as expected?
List of Product Line values =
VAR __MAX_VALUES_TO_SHOW = 100
VAR ValuesComplete =
CALCULATETABLE (
VALUES ( 'allChart'[Product Line] ),
ALL ( 'allChart'[Product Line] )
)
VAR __DISTINCT_VALUES_COUNT =
COUNTROWS ( ValuesComplete )
VAR DisplayEtc =
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW
VAR Result =
CONCATENATEX (
-- TopN can be used even if num items is smaller than max
TOPN (
__MAX_VALUES_TO_SHOW,
ValuesComplete,
'allChart'[Product Line], ASC
),
'allChart'[Product Line],
", ",
'allChart'[Product Line], ASC
)
& IF (
DisplayEtc,
", etc."
)
RETURN
Result
Hi @Brett007
The solution I would suggest involves using ALL ( 'allChart'[Product Line] ) to clear the filter on Product Line when determining the list of Product Line values
In the code below, ValuesComplete is a list of Product Lines with the Product Line filter cleared.
I also rewrote the code slightly, using the fact to TOPN will return all the values if there are fewer than N.
Does this work as expected?
List of Product Line values =
VAR __MAX_VALUES_TO_SHOW = 100
VAR ValuesComplete =
CALCULATETABLE (
VALUES ( 'allChart'[Product Line] ),
ALL ( 'allChart'[Product Line] )
)
VAR __DISTINCT_VALUES_COUNT =
COUNTROWS ( ValuesComplete )
VAR DisplayEtc =
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW
VAR Result =
CONCATENATEX (
-- TopN can be used even if num items is smaller than max
TOPN (
__MAX_VALUES_TO_SHOW,
ValuesComplete,
'allChart'[Product Line], ASC
),
'allChart'[Product Line],
", ",
'allChart'[Product Line], ASC
)
& IF (
DisplayEtc,
", etc."
)
RETURN
Result
Works perfectly. Thank you.
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |