cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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:

PowerBi Image.PNG

 

SSAS Tabular Data Model:

SSAS Tabular Model relationships.PNG

Sample Fact table data:

SampleFactData.PNG

 

DimLocation:

DimLocation.PNG

DimStatus

DImStatus.PNG

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Power BI filtering issues in matrix

Hi @vamsikrishna007 ,

 

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_ )

 

v-xuding-msft_0-1597919551751.png

 

 

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

View solution in original post

2 REPLIES 2
Highlighted
Frequent Visitor

Re: Power BI filtering issues in matrix

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.

Highlighted
Community Support
Community Support

Re: Power BI filtering issues in matrix

Hi @vamsikrishna007 ,

 

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_ )

 

v-xuding-msft_0-1597919551751.png

 

 

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

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors