Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

How do I count values based on their counted attribute?

I want to know how many entries a certain dimension has in a fact table under certain conditions. So I created a table and I have a list of the Id's of the dimension and I have a count of the corresponding entries in the fact table with the requires filters on the visual.

 

What I want to see is how many Id's have corresponding entries in bins, such as >10, 10-25, 25-50, etc

 

My table looks like this:

 

identries
11129
11112
111327
111422
11189

 

And i'm trying to see some representation of this with all the filters:

COUNT id's with entries < 10: 3

COUNT id's with entries > 10: 2

 

How can I have this sort of visualization?

 

1 ACCEPTED SOLUTION
ssugar
Resolver III
Resolver III

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:

community-sol-279222.png

pbix file with this is available here: https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-588769.pbix

View solution in original post

1 REPLY 1
ssugar
Resolver III
Resolver III

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:

community-sol-279222.png

pbix file with this is available here: https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-588769.pbix

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.