Forum Discussion

saivina2920's avatar
saivina2920
Post Prodigy
5 years ago
Solved

count data mismatching in Table Chart

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 t...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
saivina2920
Post Prodigy
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    saivina2920
    Post Prodigy
    5 years ago

    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.

     

     

    • saivina2920's avatar
      saivina2920
      Post Prodigy
      5 years ago

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

    • saivina2920's avatar
      saivina2920
      Post Prodigy
      5 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        saivina2920
        Post Prodigy
        5 years ago

        Hi Anonymous ,

         

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