Power BI filtering issues with matrix

[vamsikrishna007]

How to display 0's instead of NULL or BLanks in a matrix in Power BI? 

Matrix is filtered based on the month selected (Left side visual). When selected January, in matrix, on expanding the Location R100, there are values for Jan 06, Jan 27 which is correct. But I don’t have to show Jan 13 and Jan 21, because there are no sessions scheduled for those dates. How can i achieve it?

DAX (Measure): DistinctRosterCount:= IF(CALCULATE(DISTINCTCOUNT(FactJobClubOrientation[RosterID])) = BLANK(), 0, CALCULATE(DISTINCTCOUNT(FactJobClubOrientation[RosterID]))).

This is the DAX behind for displaying the values in the Matrix. I can’t replace the 0 in DAX with blank or null because as per the business i have to display 0 in Power BI even though the distinct count is null for a location. 

In Matrix, on the rows, I am displaying Location from DimLocation table, SessionDate1 from DimDate table. On the Columns, displaying Description from the DimStatus table (Both Show & NoShow), on the values, I am displaying measure DistinctRosterCount wrote in the Fact table.

Note: "Show items with nodata is turned off" on the rows and columns in the matrix.

SSAS 2017, PBI Sep 2019 versions.

The data source for Power BI report is from the SSAS server. 

 

[v-zhenbw-msft]

Hi @vamsikrishna007 ,

Don't you want to display the line that is all 0?

If yes, maybe you don’t need to use IF function, just try this measure.

DistinctRosterCount:= CALCULATE(DISTINCTCOUNT(FactJobClubOrientation[RosterID]))

Or you can put this measure to filter on this page, and configure the measure is not 0.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[vamsikrishna007 · Accepted solution]

@v-zhenbw-msft 

Thansk for suggestion. But it wont fit for my business requirement.

I always have to display all the locations, and if there is a session i want to show the dates. If there's no session then display 0.

But i figured out the way to achieve it. Thanks

# Count = 
// Orginal Measure: Count Unique IDs
VAR __COUNTROSTER = DISTINCTCOUNT('FactJobClubOrientation'[RosterID])

// Second Measuse: Count Regardless of show or no show, to see if there was a session
VAR __COUNTALLROSTER = CALCULATE(DISTINCTCOUNT('FactJobClubOrientation'[RosterID]), ALL('DimStatus'[Description]))

// Third: Show 0 for all locations, if there's no session.
VAR __NOROSTER = IF ( NOT ISINSCOPE('DimDate'[SessionDate]) , CALCULATE( 0 , ALL(DimLocation)))

RETURN
// If there was a sesssion, show the measure with 0 instead of blanks
IF ( __COUNTALLROSTER > 0 , IF ( ISBLANK(__COUNTROSTER) , 0 , __COUNTROSTER), __NOROSTER)