topic Re: Include all created age groups in the table visual even if no data present for any of the age group in DAX Commands and Tips

Include all created age groups in the table visual even if no data present for any of the age group

Anonymous — Mon, 06 Sep 2021 10:14:10 GMT

I have age groups say 5-10, 10-20, 20-25, and so on, but for some age groups, no record is present in the table. How can I include those age groups into the table visual?

eg: table visual not including 20-25 age group as no record is present

Age Groups	Attended Conference	Not attended conference
5-10	20	40
10-20	50	10
Total	70	50

what I want

Age Groups	Attended Conference	Not attended conference
5-10	20	40
10-20	50	10
20-25	0	0
Total	70	60

Re: Include all created age groups in the table visual even if no data present for any of the age group

goncalogeraldes — Mon, 06 Sep 2021 10:51:46 GMT

Hello there Anonymous ! For the groups with missing values are the values set to "null" or 0 (zero)? If I understood your problem correctly you want to display blank space in a table visual even if the age group doesn't have a value. If you're using table visual you can click on a dropdown on the field and click 'Don't summarize'. This will show you all the values including nulls.

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

Re: Include all created age groups in the table visual even if no data present for any of the age group

Anonymous — Mon, 06 Sep 2021 11:10:33 GMT

Thank you goncalogeraldes for your quick reply. The age groups I have created using switch function which takes a person age as input.

eg: age_group = SWITCH( TRUE(), table1[age] >=5 && table1[age]<=10), "5-10") like this other groups are created.

In the records as no person's age lies between 20-25 group so this group is not created as a result "20-25" is not displaying while using table visuals. I wanted to know is there any way I can include "20-25" also in the table visual.

Re: Include all created age groups in the table visual even if no data present for any of the age group

goncalogeraldes — Mon, 06 Sep 2021 11:16:35 GMT

Hey there Anonymous ! Have you tried this?

Check out this Microsoft documentation link for further information:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

Re: Include all created age groups in the table visual even if no data present for any of the age group

Anonymous — Mon, 06 Sep 2021 11:20:16 GMT

goncalogeraldes yes, I tried with "Show items with no data" didn't work out for me

Re: Include all created age groups in the table visual even if no data present for any of the age group

goncalogeraldes — Mon, 06 Sep 2021 11:34:37 GMT

Anonymous I would try to create a table/list/dimension with all the age groups and connect it to table1. Then use it in the table visual instead of the field in table1. This way the groups with no data would appear empty! As an analytical suggestion I would recommend you to change the range of your age groups to

5 - 10

11 - 15

16 - 20

21 - 25

Bins should always have the same size for comparision reasons and never have a common upper/lower limit!

Re: Include all created age groups in the table visual even if no data present for any of the age group

Anonymous — Mon, 06 Sep 2021 12:15:14 GMT

goncalogeraldes Thank you for your solution, but I was trying to do it with DAX or any other tools of power BI instead of creating a new table. The approach I was trying to use is the one we use as IF(ISBLANK(measure),0, measure)..... something related to this, like if a specific age group is blank still assign the value to it.

But I think so the solution I want is not possible without including all the age groups in the table.

Thanks for your suggestions related to bins, it was really helpful.