Forum Discussion

DebbieE's avatar
DebbieE
Icon for Community Champion rankCommunity Champion
2 years ago
Solved

Counting Values less that a certain number. can I also include the slicers in the DAX

I have a visual whenre I add Number of People against Areas. If the total is less than wo I dont display it.

 

I have some DAX that counts the less than or equal to 10s

Is Area 10 or below = CALCULATE(sumx(VALUES('Dim Person'[Area]),IF('Fact'[Total People]<=10,1,BLANK())))
 
this works. However I have a Slicer on Year and Month from the date table set to 2024 January
And Another Slicer. Lets say' District. and this actually contains 20 areas.
On this I have a bar chart and I filter out anything under 10. 
 
I have a card showing the number of People in that group of under 10s. However the actual DAX is bringing back everything. ALL districts so im getting a lot of numbers.
 
I want the DaX toinclude the Date Year and district Slicer values so it only counts those related to the page. I have Tried ALL and ALLSELECTED but its just not working.
 
Has anyone got any ideas on how I could sort out the DAX above to include the slicers on the page?
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    DebbieE , Hope [Total People] is just a sum measure not using all or allselected. If all and all selected are not used then if the measure used anything other than area, then it will give data that will consider that row context too

     

    based on what I got

     

    so your total measure can be

    = calculate(Count(Table[People]), filter(allselected(Dim Person), 'Dim Person'[Area] = Max('Dim Person'[Area]) ) )

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    DebbieE , Hope [Total People] is just a sum measure not using all or allselected. If all and all selected are not used then if the measure used anything other than area, then it will give data that will consider that row context too

     

    based on what I got

     

    so your total measure can be

    = calculate(Count(Table[People]), filter(allselected(Dim Person), 'Dim Person'[Area] = Max('Dim Person'[Area]) ) )

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.