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 have a table with transactions and want to count the transactions by country to then group those and then use as a filter.
I got to the transaction count using a measure:
country_count =
Solved! Go to Solution.
Hi @joao_costa ,
Here's my solution.
1.country_count is measure, and is created as
country_count = CALCULATE(DISTINCTCOUNT('Table'[trx_id]),FILTER(ALLSELECTED('Table'),[Country]=MAX('Table'[Country])))
2.Create a calculated table using dax
Table 2 = GENERATESERIES ( 1, MAXX('Table',[country_count]))
3.Then create a measure, put it in the filter, set show items when the value is 1.
Measure = IF([country_count]<=MAX('Table 2'[Value])&&MIN('Table 2'[Value])<=[country_count],1)
4.The slicer is created using value field from Table 2. No relationship between two tables. The slicer can move the slider to filter.
Check more details from the attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @joao_costa ,
Here's my solution.
1.country_count is measure, and is created as
country_count = CALCULATE(DISTINCTCOUNT('Table'[trx_id]),FILTER(ALLSELECTED('Table'),[Country]=MAX('Table'[Country])))
2.Create a calculated table using dax
Table 2 = GENERATESERIES ( 1, MAXX('Table',[country_count]))
3.Then create a measure, put it in the filter, set show items when the value is 1.
Measure = IF([country_count]<=MAX('Table 2'[Value])&&MIN('Table 2'[Value])<=[country_count],1)
4.The slicer is created using value field from Table 2. No relationship between two tables. The slicer can move the slider to filter.
Check more details from the attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Created a Calculation Group with Each of your grouping (and the logic) as calculated items. Then use the calc group as your slicer.
The DAX for each item looks like this. I parameterized the bounds so you can use same logic in each item, just change the bounds. Watch out for overlapping items though
VAR _lowerBound = 0
VAR _upperBound = 4
VAR _summary =
CALCULATETABLE (
VALUES ( 'txn'[country] ),
FILTER (
SUMMARIZE ( 'txn', 'txn'[country], "_count", DISTINCTCOUNT ( 'txn'[Index] ) ),
[_count] >= _lowerBound
&& [_count] <= _upperBound
)
)
RETURN
CALCULATE(SELECTEDMEASURE(), _summary)
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |