Forum Discussion

TCatron18's avatar
TCatron18
Helper II
1 year ago
Solved

Matrix to Not Display Months Without Data

Hello!

I have a matrix with several measures displaying YTD data by month. I am having an issue with one of my measures populating data in months that don't have any other relevant data. 

I was able to create this measure for one of the scenarios I needed to account for. That scenario being that another measure was equal to 1.

Total Emp Count =
VAR MinDate = FIRSTDATE ( 'Date'[Date] )
VAR MaxDate = LASTDATE ( 'Date'[Date] )
VAR SysGen = [Measure 1]
VAR EmpCount =
CALCULATE (
COUNTROWS ( 'Census' ),
( 'Census'[DATE] >= MinDate
&& 'Census'[DATE] <= MaxDate )
)
Return
IF( SysGen = 1, 1, EmpCount - [Measure 2] )
 
The measure I am having the issue with is the following. In this scenario, if the row value equals 'xSystem Generated' then I was it to populate 1 otherwise populate the appropriate value. I can't use the same logic as above because for this section of the matrix [Measure 1] does not equal 1. The below does populate a 1 in the field I need it to, but it's also populating it in future months that do not have any other applicable data.
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 )
)
RETURN
IF ( SysGen = "xSystem Generated", 1, EECount )
 
Any help is truly appreciated!
Thanks!!
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    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

11 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

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

    • TCatron18's avatar
      TCatron18
      Helper II
      1 year ago

      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.

       

      • bhanu_gautam's avatar
        bhanu_gautam
        Super User
        1 year ago

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

  • Ashish_Excel's avatar
    Ashish_Excel
    Solution Supplier
    1 year ago

    Hi,

    Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.