Matrix to Not Display Months Without Data

[TCatron18]

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!!

[TCatron18]

@Ashish_Excel Here is my sample data pbix. I am looking for the Total EE Count (or Total EE Count TEST - this is using the new code provided by @bhanu_gautam) to display '1' in that column when the Company is 'xSystem Generated'. 

https://drive.google.com/file/d/1hSHiz73gTE10p_GDFwSVOaonXKakXElk/view?usp=sharing 

[Ashish_Excel]

Access Denied message.

[TCatron18]

@Ashish_Excel Apologies. I've updated the sharing. 

[Ashish_Excel]

Your question has already been answered by another user.

[Ashish_Excel]

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.

[bhanu_gautam]

@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]

@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]

@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]

@bhanu_gautam I don't see that there is a difference between this DAX and the original one you posted. Am I missing something? 

[v-achippa · Accepted solution]

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

[TCatron18]

Hi @v-achippa  - Thank you that worked perfectly 🙂