count data mismatching in Table Chart

[saivina2920]

I am getting some wrong count in the chart table. Example, i have given the below. 

sample Test File and data Link : https://1drv.ms/u/s!AiSRcgO5FUmN8USBS75RKolqBQDT?e=HBxCu6 

What is the problem.?

[Greg_Deckler]

@saivina2920 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[saivina2920]

Thanks for your help.

When you are look at my Test pbix file, it's showing more emp_no record. also, the future date count should not come.

can you pls. check the file and give us th correct count please..?

[Anonymous]

Hi @saivina2920 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

Measure = 
VAR _selemp =
    SELECTEDVALUE ( 'EMP_TABLE'[EMP_NO] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'EMP_TABLE'[EMP_STATUS] ),
        FILTER ( ALLSELECTED ( 'EMP_TABLE' ), 'EMP_TABLE'[EMP_NO] = _selemp )
    )
VAR _selreldate =
    SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] )
RETURN
    IF (
        _selreldate > [vToday]
            || _count > 1,
        BLANK (),
        DATEDIFF ( SELECTEDVALUE ( EMP_TABLE[EMP_RESIGN_DATE] ), TODAY (), DAY )
    )

Best Regards

[saivina2920]

Thanks for your quick reply. I modified as per my requirement.

Output coming perfect except few points which is highlighted below.

pls. find the latest test file with data in the following link.

https://1drv.ms/u/s!AiSRcgO5FUmN8UiVPTOFR_R4KHIl?e=az7Elh 

Output coming perfect except few points which is highlighted below.

kindly let me know if you need any more details.

[Anonymous]

Hi @saivina2920 ,

I have updated your sample pbix file(see attachment), please check whether it can get the correct result.

Update the formula of calculated column [DurDays] as below

DurDays = 
IF (
    EMP_TABLE[EMP_RESIGN_DATE] > [vToday]
        || CALCULATE (
            [BalCount],
            ALLEXCEPT ( 'EMP_TABLE', 'EMP_TABLE'[EMP_NO], 'EMP_TABLE'[EMP_RESIGN_DATE] )
        ) = 0,
    BLANK (),
    DATEDIFF ( EMP_TABLE[EMP_RESIGN_DATE], TODAY (), DAY )
)

Or you can create the below 2 measures to get it:

Measure = 
VAR _selreldate =
    SELECTEDVALUE ( EMP_TABLE[EMP_RESIGN_DATE] )
RETURN
    IF (
        _selreldate > [vToday]
            || [BalCount] =0,
        BLANK (),
        DATEDIFF ( _selreldate, TODAY (), DAY )
    )

Duration days = SUMX(GROUPBY('EMP_TABLE','EMP_TABLE'[EMP_NO],'EMP_TABLE'[EMP_RESIGN_DATE]),[Measure])

Best Regards

[saivina2920]

Hi @Anonymous ,

Still the count is not accurate. pls. refer the below screenshot for clear reference.

[Anonymous]

Hi @saivina2920 ，

Please create another measure as below to get Balcount to exclude the future dates:

TempBalCount = 
VAR _selreldate =
    SELECTEDVALUE ( EMP_TABLE[EMP_RESIGN_DATE] )
RETURN
    IF ( _selreldate > [vToday], 0, [XTOY] - [YTOX] )

Then update the formula of measure [BalCount] as below:

BalCount = SUMX(GROUPBY('EMP_TABLE','EMP_TABLE'[EMP_NO],'EMP_TABLE'[EMP_RESIGN_DATE]),[TempBalCount])

Best Regards

[saivina2920]

Thanks for your quick reply. Everyting is perfect except one.

We want to display only two records and remaining want to exclude.

Because, A-8 = C (c count rows only two records. that's why)

pls. refer the below screenshot.

[Anonymous]

Hi @saivina2920 ,

Please update the formula of measure [TempBalCount] as below:

TempBalCount =

VAR _selreldate =

SELECTEDVALUE ( EMP_TABLE[EMP_RESIGN_DATE] )

RETURN

IF (

[XTOY] - [YTOX] = 0,

BLANK (),

IF ( _selreldate > [vToday], 0, [XTOY] - [YTOX] )

)

Best Regards

[saivina2920]

awesome results.

In the below screenshot, when user selects "EMP_STATE" then only it should show the records in the table. otherwise no need to display records in the table.

How to apply this logic..?

If this works, then mine is over.

[Anonymous · Accepted solution]

Hi @saivina2920 ，

Please update the formula of measure [TempBalCount] as below and check whether it can get your expected result.... You can find the attachment for the details. 

TempBalCount =

VAR _selreldate =

SELECTEDVALUE ( EMP_TABLE[EMP_RESIGN_DATE] )

RETURN

IF (

ISFILTERED ( 'EMP_TABLE'[EMP_STATE] ),

IF (

[XTOY] = [YTOX],

BLANK (),

IF ( _selreldate > [vToday], 0, [XTOY] - [YTOX] )

),

BLANK ()

)

Also, it is worth noting that if you want to implement this "dynamic" feature it is not appropriate to get the duration days by creating a calculated column [DurDays]. So you have to use the measure [Duration days] instead of the calculated column [DurDays]. For more information about the difference between calculated columns and measure, you can read the following blogs.

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

Best Regards

[saivina2920]

It's Awesome and it's fantastic logic with favourable reply.

[saivina2920]

Hi @Anonymous ,

Can you pls. give us the idea to remove the unneccessary records from the list. (pls. refer the screenshot in the highlighted cell).

thanks for your spending golden time.

[saivina2920]

pls. tell us, how to remove the highligted unmatching data's..