Forum Discussion
Matrix to Not Display Months Without Data
Hi TCatron18,
Thank you for reaching out to Microsoft Fabric Community.
Based on your requirement, here the issue is like the xSystem Generated rows showing as 1 even when no actual data exists for that month. Please use below measure:
Total EE Count =
VAR MinDate = FIRSTDATE('Date'[Date])
VAR MaxDate = LASTDATE('Date'[Date])
VAR SysGen = SELECTEDVALUE('Table'[Value])
VAR EECount =
CALCULATE (
COUNTROWS('Census'),
'Census'[Date] >= MinDate && 'Census'[Date] <= MaxDate
)
VAR HasData =
CALCULATE (
COUNTROWS('Census'),
REMOVEFILTERS('Table'[Value]),
'Census'[Date] >= MinDate && 'Census'[Date] <= MaxDate
) > 0
RETURN
IF (
HasData,
IF ( SysGen = "xSystem Generated", 1, EECount ),
BLANK()
)
This will check whether any real census data exists for the current month and return 1 if valid.
Make sure that the 'Show items with no data' option is disabled for the month columns in the matrix visual.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
bhanu_gautam That does move the future months where no data exists; however, it is not populating the '1' in the field when the row value = xSystem Generated.
TCatron18 , Try using
dax
Total EE Count =
VAR MinDate = FIRSTDATE ( 'Date'[Date] )
VAR MaxDate = LASTDATE ( 'Date'[Date] )
VAR SysGen = SELECTEDVALUE ( 'Table'[Value] )
VAR HasData =
CALCULATE (
COUNTROWS ( 'Census' ),
( 'Census'[Date] >= MinDate && 'Census'[Date] <= MaxDate )
) > 0
VAR EECount =
CALCULATE (
COUNTROWS ( 'Census' ),
( 'Census'[Date] >= MinDate && 'Census'[Date] <= MaxDate )
)
RETURN
IF ( HasData, IF ( SysGen = "xSystem Generated", 1, EECount ), BLANK() )
bhanu_gautam I don't see that there is a difference between this DAX and the original one you posted. Am I missing something?
Hi TCatron18,
Thank you for reaching out to Microsoft Fabric Community.
Based on your requirement, here the issue is like the xSystem Generated rows showing as 1 even when no actual data exists for that month. Please use below measure:
Total EE Count =
VAR MinDate = FIRSTDATE('Date'[Date])
VAR MaxDate = LASTDATE('Date'[Date])
VAR SysGen = SELECTEDVALUE('Table'[Value])
VAR EECount =
CALCULATE (
COUNTROWS('Census'),
'Census'[Date] >= MinDate && 'Census'[Date] <= MaxDate
)
VAR HasData =
CALCULATE (
COUNTROWS('Census'),
REMOVEFILTERS('Table'[Value]),
'Census'[Date] >= MinDate && 'Census'[Date] <= MaxDate
) > 0
RETURN
IF (
HasData,
IF ( SysGen = "xSystem Generated", 1, EECount ),
BLANK()
)
This will check whether any real census data exists for the current month and return 1 if valid.
Make sure that the 'Show items with no data' option is disabled for the month columns in the matrix visual.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa