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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RA_gov
Frequent Visitor

Is it possible to add counts next to slicer field?

How would I add a 'count' of the slicer values as an additional field in the slicer?
I have a map report with properties in US states.  My current slicer is STATE.
I want to add the count of properties in each STATE on my slicer to assist my users.  I need guidance on how to do this.

What I am looking to do:

[check box]   AK  4
[check box]   AL  7
[check box]   AZ  2
[check box]   CA  9

 

Thanks.

3 ACCEPTED SOLUTIONS

Hi @RA_gov,

 

Replace the ";" by "," depending on the regional settings PBI (as EXCEL) uses the dot comma or the comma to make the split between formulas parameters.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

RA_gov
Frequent Visitor

@MFelix

State Count = Table1[state_code] & "  "
    & CALCULATE ( COUNT ( Table1[state_code] ), ALLEXCEPT (Table1, Table1[state_code]  ) )

 

This format works!  commas replacing semicolons for US based regional setting.

 

Thank you.  Well done.

View solution in original post

Hi @RA_gov,

 

Maybe formatting the number as 0000:

 

State Count = Table1[State] & " " & FORMAT(CALCULATE ( COUNT ( Table1[State] ); ALLEXCEPT ( Table1; Table1[State] ) );"000#")

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

Hi @RA_gov,

 

You need to add an addtional column to your model that calculates the number of values and concatenate with the State. Be aware that this is not dinamic and the count is based on the full data.

 

State Count =
Table1[State] & " "
    & CALCULATE ( COUNT ( Table1[State] ); ALLEXCEPT ( Table1; Table1[State] ) )

 

Then use this new column in the slicer.

 

If you have a states dimension table that is related with the other table add the column in that table with the following code:

Column = States[State] &" " &CALCULATE(COUNT(Table1[State]))

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



RA_gov
Frequent Visitor

@MFelix

 

When I remove the semicolons ';' and the ALLEXCEPT clause, my counts for each STATE =1.

When I insert your text as written, I get:

The syntax for ';' is incorrect. (DAX(dbo_active_property[state_code] & " " & CALCULATE ( COUNT ( dbo_active_property[state_code] ); ALLEXCEPT ( dbo_active_property; dbo_active_property[state_code] ) ))).

 

Any hints?


Thanks

RA_gov
Frequent Visitor

@MFelix

State Count = Table1[state_code] & "  "
    & CALCULATE ( COUNT ( Table1[state_code] ), ALLEXCEPT (Table1, Table1[state_code]  ) )

 

This format works!  commas replacing semicolons for US based regional setting.

 

Thank you.  Well done.

Hi @RA_gov,

 

Replace the ";" by "," depending on the regional settings PBI (as EXCEL) uses the dot comma or the comma to make the split between formulas parameters.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



RA_gov
Frequent Visitor

Your solution works.  The numbers however are not 'right justified' so it appears a bit confusing to the viewer.

AK 73

AL 537

AR 432

AZ 320

CA 2259

 

Do you have a recommendation to make it appear like this:

AK       73

AL     537

AR    432

AZ    320

CA  2259

 

Thanks,

Hi @RA_gov,

 

Maybe formatting the number as 0000:

 

State Count = Table1[State] & " " & FORMAT(CALCULATE ( COUNT ( Table1[State] ); ALLEXCEPT ( Table1; Table1[State] ) );"000#")

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



RA_gov
Frequent Visitor

@MFelix

Good solution.  Not perfect, but looks much better to viewer.
Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.