Power BI filtering issues with matrix

Question

Can any one please help me out.

See the attached images.

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?

For Locations Del Paso, I don’t have to display any January dates as no sessions happened for those locations on those dates.

Sample Fact Data

PowerBI Image

SSAS Tabular Model Relationships

January

June

SSAS 2017, PBI Sep 2019 versions.

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

Thanks,

Vamsi

TJ_ TJ_ · Accepted Answer · 2020-08-06T17:18:58

The matrix is only showing the rows if there is a result for that row. You might think: It's 0 so there is no result. But that's wrong: 0 is also a valid result. In order to remove the line, you have to make sure the result is empty, also known as NULL in databases and BLANK in DAX.

Since you didn't post the DAX of your measures, some suggestions:

Remove the 0's in your source data. Not sure if you have access to the data, but you can replace the 0's with null/blank values in either PowerQuery or SQL.
We can't see the source data (what does the data in the fact table look like?), but i'm guessing that's where the real 'problem' lies.
Make an adjustment to the measure. Not the most beautiful solution, but it works. pseudo code:

VAR _Show = COUNT ( 'Fact'[ID] )
RETURN
IF ( _Show = 0 , BLANK(), _Show)

But, again, we need more info about the data in your fact table and the measures you already created.

Edit:

You already replaced BLANKS with 0, and set the 'Show items with no data' on. Then the measure above will do no good.

Suggestion:

Turn 'Show items with no data' off!

New measure:

(assumtion that DimStatus[Description] is on the column

# 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]))

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

Third suggestion:

You want to show all the location with 0, if there ar no shows. I've include an ISINSCOPE check for DimDate, to prevent it's showing 0 when collapsing the location.

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

Power BI filtering issues with matrix

1 Answers