topic Re: count data mismatching in Table Chart in Developer

count data mismatching in Table Chart

saivina2920 — Fri, 13 Aug 2021 14:08:38 GMT

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.?

Re: count data mismatching in Table Chart

Greg_Deckler — Fri, 13 Aug 2021 14:38:42 GMT

@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

Re: count data mismatching in Table Chart

saivina2920 — Fri, 13 Aug 2021 16:29:04 GMT

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..?

Re: count data mismatching in Table Chart

Anonymous — Mon, 16 Aug 2021 10:25:28 GMT

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

Re: count data mismatching in Table Chart

saivina2920 — Mon, 16 Aug 2021 15:39:25 GMT

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.

Re: count data mismatching in Table Chart

saivina2920 — Mon, 16 Aug 2021 15:43:57 GMT

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

Re: count data mismatching in Table Chart

saivina2920 — Tue, 17 Aug 2021 02:19:52 GMT

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.

Re: count data mismatching in Table Chart

Anonymous — Tue, 17 Aug 2021 02:25:30 GMT

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

Re: count data mismatching in Table Chart

saivina2920 — Tue, 17 Aug 2021 02:53:06 GMT

Hi @Anonymous ,

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

Re: count data mismatching in Table Chart

Anonymous — Tue, 17 Aug 2021 03:19:34 GMT

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

Re: count data mismatching in Table Chart

saivina2920 — Tue, 17 Aug 2021 04:16:08 GMT

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.

Re: count data mismatching in Table Chart

Anonymous — Tue, 17 Aug 2021 05:18:25 GMT

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

Re: count data mismatching in Table Chart

saivina2920 — Tue, 17 Aug 2021 09:48:24 GMT

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.

Re: count data mismatching in Table Chart

Anonymous — Wed, 18 Aug 2021 06:13:27 GMT

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

Re: count data mismatching in Table Chart

saivina2920 — Wed, 18 Aug 2021 15:22:12 GMT

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