Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI filtering issues in matrix

Matrix in the Power BI will be 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. 

 

Attached all the images.

 

PBI:

 

SSAS Tabular Data Model:

 

Sample Fact table data:

 

DimLocation:

 

DimStatus

 

 

Thanks.

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

     

    Please try to add a visual filter in Filter Pane like this:

     

    Measure = 
VAR COUNT_ =
    CALCULATE ( DISTINCTCOUNT ( FactJobClubOrientation[RosterID] ) )
RETURN
            IF ( ISBLANK ( COUNT_ ), 0, COUNT_ )

     

     

     

2 Replies

  • DataMozart's avatar
    DataMozart
    Frequent Visitor
    6 years ago

    Hi vamsikrishna007,

     

    As you wrote: 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...

     

    0 is a value, same as 1, 10, 100... so if you intentionally put it in your DAX measure instead of BLANK, then there is no way that you don't display it, as long as you use "regular" date dimension with contiguous dates in it. 

     

    The only way to hide those rows is to use filters and choose to display only those values of the measure > 0.

     

    Hope that this helps.

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Please try to add a visual filter in Filter Pane like this:

     

    Measure = 
VAR COUNT_ =
    CALCULATE ( DISTINCTCOUNT ( FactJobClubOrientation[RosterID] ) )
RETURN
            IF ( ISBLANK ( COUNT_ ), 0, COUNT_ )