Is it possible to add counts next to slicer field?

[RA_gov]

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.

[MFelix · Accepted solution]

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

[RA_gov · Accepted solution]

@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.

[MFelix]

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

[RA_gov]

@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 · Accepted solution]

@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.

[MFelix · Accepted solution]

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

[RA_gov]

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,

[MFelix · Accepted solution]

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

[RA_gov]

@MFelix

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