Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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_ )
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_ )
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |