Forum Discussion
How do I count values based on their counted attribute?
- 7 years ago
To get this working, I did the following:
1. Created a new table (enter data) with two columns to specify the greater than and less than numbers that define the bounds of each bin.
2. I then created a calculated column (modeling > new column) with the following DAX:
Bin = format(Table2[Bin GT], "general number") & "-" & format(Table2[Bin LT], "general number")
3. I then created two new measures for the selected Bin GT and selected Bin LT so I could reference those in a filter on the table with your data:
Selected Bin GT = SELECTEDVALUE(Table2[Bin GT])
Selected Bin LT = SELECTEDVALUE(Table2[Bin LT])
4. Then I created another measure in the table with your data:
Count of Entries in Bin = CALCULATE(count(Table1[id]), filter(Table1, Table1[entries] > [Selected Bin GT] && Table1[entries] < [Selected Bin LT]))
5. Finally, I added the Bin calculated column as an axis on a bar chart, and the Count of Entries in Bin measure as the value. That got me to this:
pbix file with this is available here: https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-588769.pbix
To get this working, I did the following:
1. Created a new table (enter data) with two columns to specify the greater than and less than numbers that define the bounds of each bin.
2. I then created a calculated column (modeling > new column) with the following DAX:
Bin = format(Table2[Bin GT], "general number") & "-" & format(Table2[Bin LT], "general number")
3. I then created two new measures for the selected Bin GT and selected Bin LT so I could reference those in a filter on the table with your data:
Selected Bin GT = SELECTEDVALUE(Table2[Bin GT])
Selected Bin LT = SELECTEDVALUE(Table2[Bin LT])
4. Then I created another measure in the table with your data:
Count of Entries in Bin = CALCULATE(count(Table1[id]), filter(Table1, Table1[entries] > [Selected Bin GT] && Table1[entries] < [Selected Bin LT]))
5. Finally, I added the Bin calculated column as an axis on a bar chart, and the Count of Entries in Bin measure as the value. That got me to this:
pbix file with this is available here: https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-588769.pbix