Forum Discussion

Pushpendra_S_R's avatar
Pushpendra_S_R
Regular Visitor
4 years ago

All Page Filter not working as expected

I have multiple tables joined with many to many relation on Business unit. Also, I have created a all page filter of Business unit but on few charts if I apply the all page filter on Business unit it filters the data partially, like only the selected Business unit will appear but  for other Business unit instaed of showing no data the filter shows the partial data - as shown below, few highlighted in yellow.

I have already checked the Show items with no Data is not checked and yes I am using measure but it does not return anything only update the existing value using Format fucntion, say

FORMAT(sum(Table[Field]),"##,###,###,###") or
FORMAT(sum(Table[Field]),"##,###,###,##0.0")
 

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Pushpendra_S_R , Your two measures giving some data for those rows. Are they using coalesce, 1+ Divide() or divide() -1

     

    If you measure divide(a,b) -1 then change it to divide(a-b,b)  and try

     

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

    • Pushpendra_S_R's avatar
      Pushpendra_S_R
      Regular Visitor
      4 years ago

      amitchandak, not using any of the above mentioned method, my measure is as followed

       

      Measure =
      IF(ISFILTERED(Units[Units]) == FALSE,
      FORMAT(sum(Table[Field]),"##,###,###,###"))
      & IF(SELECTEDVALUE(Units[Units]) == "Billion",
      FORMAT(sum(Table[Field])/SUM(Units[Values]), "##,###,###,###" & "B" ))
      & IF(SELECTEDVALUE(Units[Units]) == "Million",
      FORMAT(sum(Table[Field])/SUM(Units[Values]), "##,###,###,###" & "M" ))
      & IF(SELECTEDVALUE(Units[Units]) == "Thousand",
      FORMAT(sum(Table[Field])/SUM(Units[Values]), "##,###,###,###" & "K" ))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Pushpendra_S_R ,

     

    According to your screenshots, it seems that Business Units= Blank are also displayed.

    You may try to create a measure like:

    Flag Filter = IF(MAX('Table'[Business Units])=BLANK(),1,0 )

//Or
Flag Filter = IF(LEN('Table'[Business Units])=0,1,0 )

    Then apply the measure to visual-level filter , set as "is 0".

     

     

    If it does not work, please share me with your pbix file after removing sensitive data to help us clarify your scenario.

    Refer to:

    How to Get Your Question Answered Quickly - Microsoft Power BI Community

    How to provide sample data in the Power BI Forum - Microsoft Power BI Community

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.