Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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
v-xuding-msft
Community Support
Community Support

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
v-xuding-msft
Community Support
Community Support

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.
DataMozart
Frequent Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.