How do we get same balance counts details to display in chart table

[saivina2920]

I am developing reports and difficult to get banlance count record in table chart. pls. find attached sample test .pbix file

Now it's showing 4 rows. But, it should show only 2 rows..

https://1drv.ms/u/s!AiSRcgO5FUmN8UAoAERxZXjuQJNC?e=M1OjqY 

How do i get the correct balance record in the chart table.

Also, how do we get delay counts in the chart table.

For example, if EMP_Relieve_Date is past date from current date, then it should show the count duration

                      if EMP_Relieve_Date is futire date from current date, then it should not considered the count and the count duration is zero by default.

How do we achieve this...?

Output should be below format in the chart table.

EMP_NO	EMP_RELIEVE_DATE	Delay in Days

[v-jingzhang]

Hi @saivina2920 

Sorry I don't understand your expected result. Assume we select Wales in the slicer, which two records should be the Balance([XTOY] - [YTOX]) to be shown in the table visual? 

Regards,
Community Support Team _ Jing

[saivina2920]

Thanks for youe reply. I have highlighted with correct data and formula. 

pls. refer the sample attached XL data and Pbix file as mentioned below.

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

pls.let me know if you need any more details.

A_RECORD =

VAR SELECTED = [vState]

VAR VAR1 = CALCULATE(DISTINCTCOUNT('EMP_TABLE'[EMP_NO]),FILTER(EMP_TABLE, EMP_TABLE[EMP_STATE]=SELECTED

&& (CONTAINSSTRING(EMP_TABLE[TO],"IND")) && EMP_TABLE[EMP_STATUS]="Current"))

RETURN

IF(ISBLANK(VAR1),0,VAR1)

B_RECORD =

VAR SELECTED = [vState]

VAR VAR1 = CALCULATE(DISTINCTCOUNT('EMP_TABLE'[EMP_NO]),FILTER(EMP_TABLE, EMP_TABLE[EMP_STATE]=SELECTED

&& (CONTAINSSTRING(EMP_TABLE[FROM],"IND")) && EMP_TABLE[EMP_STATUS]="Current"))

RETURN

IF(ISBLANK(VAR1),0,VAR1)

BalCount = [A_RECORD] - [B_RECORD]

Note : Key record is "EMP_NO".

[v-jingzhang · Accepted solution]

Hi @saivina2920 

Your original DurDays column didn't get the correct result because it referred to a wrong column EMP_TABLE[EMP_RELIEVE_DATE] in the DAX. After refering to the correct column as below, it returns the correct results. 

DurDays = IF(EMP_TABLE[EMP_RESIGN_DATE]>[vToday],BLANK(),DATEDIFF(EMP_TABLE[EMP_RESIGN_DATE],TODAY(),DAY))

My understanding is that C is to return the number of EMP_NOs which exist in A but don't exist in B. So your BalCount measure is able to be used as a filter field in the table visual. You can set up its value to 1 to determine which one only exists in A.

If you don't want to use BalCount as a filter field, you can use below measure instead to filter the table visual. In this formula, __A/__B/__C are tables which return the distinct EMP_NOs in each group.

Measure C = 
VAR SELECTED = [vState]
VAR __A = CALCULATETABLE(DISTINCT('EMP_TABLE'[EMP_NO]),FILTER(ALL(EMP_TABLE), EMP_TABLE[EMP_STATE]=SELECTED
 && (CONTAINSSTRING(EMP_TABLE[TO],"IND")) && EMP_TABLE[EMP_STATUS]="Current"))
VAR __B = CALCULATETABLE(DISTINCT('EMP_TABLE'[EMP_NO]),FILTER(ALL(EMP_TABLE), EMP_TABLE[EMP_STATE]=SELECTED
 && (CONTAINSSTRING(EMP_TABLE[FROM],"IND")) && EMP_TABLE[EMP_STATUS]="Current"))
VAR __C = EXCEPT(__A,__B)
RETURN
IF(SELECTEDVALUE(EMP_TABLE[EMP_NO]) IN __C, 1, 0)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[saivina2920]

Thanks for your excellent result.

[saivina2920]

Can any one update on the topic...i am struggling to move further...