Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear Forum users,
I am creating a bar chart showing the # of children in each Length of Stay cagegory, as in screenshot below.
The chart also has a field parameter slicer of child gender, age and race, that are used as legend in the chart to break down the numbers. The bar can show the value labels for each legend category and the total counts across the legend categories.
But I would also like to create a measure for the total counts disregarding the legend (gender, age, or race). Because I want to use this to create percentages that can be used for tooltips.
I can use ALLSELECTED to create a measure for any single filter/legend like gender. It is a valid measure for the total.
M_Exits_nolegend = CALCULATE( [M_Exits_Sum], ALLSELECTED(Excel_eachstate[gender]) )
But I would like to create a single DAX measure for the total counts for either gender, age or race. I tried to use the parameter field to create the DAX measure (shown below), but it has no effect.
M_Exits_noparameter = CALCULATE(
[M_Exits_Sum],
ALLSELECTED('Child Age, Gender and Race'[Child Age, Gender and Race])
)
I would appreciate any of your help on this. Is there a way to include multiple columns/fields in ALLSELECTED? Is there anyway I can revise the parameter fields
Hi @LijunChen ,
I'm not sure I'm understanding you correctly, I think you're trying to ignore the gender, age or race filter and just calculate total counts directly?
If this is the case, you can choose to use the "ALL" function to calculate, for example:
M_Exits_noparameter = CALCULATE(
[M_Exits_Sum],
ALL('YourTableName')
)
If I have misunderstood, could you please provide me with the sample data and your expected results, it would be helpful for me to understand your needs, thank you!
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-junyant-msft Thanks for your repsonse.
I am trying to ignore the gender, age or race, but just the three only. These are in a fields parameter and used in the legend. And I want to calculate the total for each of the length categories: such as "one week", "8 days to one month". I also need to taken into account the two slicer filters: one for State and the other the reporting period.
If I use All('Table Name'), the total will be for the whole table, ignoring all filters, including state, period, ad length of stay in the Y axis. I just want to create a single DAX measure for the row total for each length of stay category whether the legend is gender, race or age.
In my screenshot of the bar visual, the row total of children for "one week" is 4907 regadless of gender. I can calculate this total with a measure using ALLSELECTED (Table[Gender]), but I would like the measure to ingnore race and age too. But ALLSELECTED cannot take in multiple columns. I am not allowed to use ALLSELECTED(Table[gender],Table[age],TAble[race]).
I wonder whether this can be achieved with any other All statement like ALLEXCEPT?
Thanks.
Lijun
User | Count |
---|---|
83 | |
77 | |
71 | |
69 | |
54 |
User | Count |
---|---|
105 | |
100 | |
91 | |
79 | |
68 |