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.
Hello,
I'd like to automatically generate a table like this:
Tag | Value |
2 or more | 2 |
2 or more | 3 |
2 or more | 4 |
2 or more | 5 |
2 or more | 6 |
2 or more | 7 |
2 or more | 8 |
2 or more | 9 |
2 or more | 10 |
4 or more | 4 |
4 or more | 5 |
4 or more | 6 |
4 or more | 7 |
4 or more | 8 |
4 or more | 9 |
4 or more | 10 |
6 or more | 6 |
6 or more | 7 |
6 or more | 8 |
6 or more | 9 |
6 or more | 10 |
8 or more | 8 |
8 or more | 9 |
8 or more | 10 |
So:
Tag = "2 or more" generate rows with range 2-10
Tag = "4 or more" generate rows with range 4-10
Tag = "6 or more" generate rows with range 6-10
Tag = "8 or more" generate rows with range 8-10
Ultimately I want to link this table to my main table on the Value column, and use it to create a >= filter in a visual that is inclusive, so that the user can chose "2 or more" and get values >= 2, select "4 or more" and get values >=4, and so on.
Open to other suggestions to accomplish this as well if you think there is a better way, but would still love to know if this can be done the way I'm describing above.
Thanks!
-Marlon
Solved! Go to Solution.
Try:
n or more =
VAR _2OrMoreValues =
GENERATESERIES ( 2, 10, 1 )
VAR _Prefix2 =
SELECTCOLUMNS ( { "2 or more" }, "Param", [Value] )
VAR _2Table =
CROSSJOIN ( _Prefix2, _2OrMoreValues )
VAR _4orMoreValues =
GENERATESERIES ( 4, 10, 1 )
VAR _Prefix4 =
SELECTCOLUMNS ( { "4 or more" }, "Param", [Value] )
VAR _4Table =
CROSSJOIN ( _Prefix4, _4OrMoreValues )
VAR _6OrMoreValues =
GENERATESERIES ( 6, 10, 1 )
VAR _Prefix6 =
SELECTCOLUMNS ( { "6 or more" }, "Param", [Value] )
VAR _6Table =
CROSSJOIN ( _Prefix6, _6OrMoreValues )
VAR _8OrMoreValues =
GENERATESERIES ( 8, 10, 1 )
VAR _Prefix8 =
SELECTCOLUMNS ( { "8 or more" }, "Param", [Value] )
VAR _8Table =
CROSSJOIN ( _Prefix8, _8OrMoreValues )
RETURN
UNION ( _2Table, _4Table, _6Table, _8Table )
However, for what you seem to need it for, I suggest a different table. Any relationship with the above table wil be many-to-many, which is to be avoided.
Instead, create a table with the threshold for each "n or more", keep it unrelated and then use a measure to return the filtered values:
On or Above threshold =
CALCULATE (
[Sum value],
FILTER (
'Product Table',
[Sum value] >= SELECTEDVALUE ( 'n or more (unrelated)'[Threshold] )
)
)
Sample file attached
Proud to be a Super User!
Paul on Linkedin.
Thank you @PaulDBrown these are amazing solutions and I really appreciate the thorough detail and attached PBIX. Thank you for answering my original question and also providing me with a better way!!!
Try:
n or more =
VAR _2OrMoreValues =
GENERATESERIES ( 2, 10, 1 )
VAR _Prefix2 =
SELECTCOLUMNS ( { "2 or more" }, "Param", [Value] )
VAR _2Table =
CROSSJOIN ( _Prefix2, _2OrMoreValues )
VAR _4orMoreValues =
GENERATESERIES ( 4, 10, 1 )
VAR _Prefix4 =
SELECTCOLUMNS ( { "4 or more" }, "Param", [Value] )
VAR _4Table =
CROSSJOIN ( _Prefix4, _4OrMoreValues )
VAR _6OrMoreValues =
GENERATESERIES ( 6, 10, 1 )
VAR _Prefix6 =
SELECTCOLUMNS ( { "6 or more" }, "Param", [Value] )
VAR _6Table =
CROSSJOIN ( _Prefix6, _6OrMoreValues )
VAR _8OrMoreValues =
GENERATESERIES ( 8, 10, 1 )
VAR _Prefix8 =
SELECTCOLUMNS ( { "8 or more" }, "Param", [Value] )
VAR _8Table =
CROSSJOIN ( _Prefix8, _8OrMoreValues )
RETURN
UNION ( _2Table, _4Table, _6Table, _8Table )
However, for what you seem to need it for, I suggest a different table. Any relationship with the above table wil be many-to-many, which is to be avoided.
Instead, create a table with the threshold for each "n or more", keep it unrelated and then use a measure to return the filtered values:
On or Above threshold =
CALCULATE (
[Sum value],
FILTER (
'Product Table',
[Sum value] >= SELECTEDVALUE ( 'n or more (unrelated)'[Threshold] )
)
)
Sample file attached
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |