Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsState 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,
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYour 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.