Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have blank values in matrix if i replace with '0' and filter with slicer then i see some the not related columns are also shown Zero
MY DAX Formulae = IF(ISBLANK([Screened Subjects]),0,[Screened Subjects])
is there any DAX to control this
Solved! Go to Solution.
To get my understanding right: You want to create a column where you show the values from the Measure COMPLETED. In case there is a BLANK you want to show a 0 instead.
But, this will fill all other rows with 0 too! You could first check, if the SCREENED SUBJECTS Measure is BLANK or not.
Blank Completed = IF(ISBLANK([Screened Subjects]),BLANK(),IF(ISBLANK([Completed]),0,[Completed]))
Does this help?
Blank Completed = IF(ISBLANK([Completed Assessments]),0,[Completed Assessments])
Ok, great! I was about to write this to you now. But cool that you figured it out yourself.
@lndnbrg I Agree that my calculation is correct but when i filter with slicer extra columns are also showing Zero but they are not related to fields selected from slicer can you let m,e know how to overcome this issue
To get my understanding right: You want to create a column where you show the values from the Measure COMPLETED. In case there is a BLANK you want to show a 0 instead.
But, this will fill all other rows with 0 too! You could first check, if the SCREENED SUBJECTS Measure is BLANK or not.
Blank Completed = IF(ISBLANK([Screened Subjects]),BLANK(),IF(ISBLANK([Completed]),0,[Completed]))
Does this help?
HI @chotu27.
Can you share more detailed information and sample files? It will be help to clarify your requirement.
BTW, I think you need to modify your measure formula. Based on your screenshots, it will calculate thought all your records(include blank records).
Regards,
Xiaoxin Sheng
This behaviour really surprised me. I was under the impression if I have a slicer active the model is filtered according to the defined relationships and if I convert a value of BLANK to 0 it should only affect the entities in the current filter context. Instead the slicer is overridden and every object is shown in the visualization.
I was under the impression you had to expressly override a filter in a DAX calculation if you wanted to change the propogated filter context.
Where can I read about the reason for this behaviour?
Another trick is to add +0 in the measure. But this has the same effect. It removes the filter context the slicers provided. I would still love to know why that is. It does not make sense. If the visual truncates dimension rows where the measure is blank the logical result of adding +0 or substituting blank with 0 should be to add the missing dimension rows WITHIN THE CURRENT FILTER CONTEXT, not override the slicer filter and bring in all dimension rows as if the slicer didn't exist.
I actually tried applying the filter in the filter pane as visual, page and report level filter. If I add +0 to the measure no filter (slicer, visual, page, report) will affect what dimension rows the visual show (all of them).
Feel free to pipe in if you have a real understanding of what is going on.
Hi ,
I have the same issue as well.
I put 0+ just to make my measure 0 when blank.
But it will brings all rows which are not in the relationship at all. Weird.
Have you found solution for this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |