Calculated measure not showing sum value in a table

[Anonymous]

Hi,

Following table,

I have 2 calculated  measures, one is No of Required Person another one is No of Employees selected.

No of Employees selected measure is not showing the total value correctly.  if I click the date filter as any value not showing the total value also 

Sample Pbix File:

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EbOhY07Tbt1IqrxMmLWum1EB5Vkq...

[Anonymous · Accepted solution]

Hi,

This is not my Expected Output.

                                                No of Employees Selected =

No of Employee Selected = CALCULATE(COUNT(Sheet2[Employee ID ]),FILTER(ALLSELECTED(sheet2),Sheet2[Department]=MAX(Sheet1[Department ID]))

)

This measure having some logics.

Pbix file:

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EbOhY07Tbt1IqrxMmLWum1EB5Vkq...

Thanks in Advance

Yuvaraj

[Anonymous]

Hi @Anonymous ,

Is your expected result will be

1

2

1

1

---

5  - Total

If yes, then there are 2 ways to achieve this

1) Change the measure as below

No of Employee Selected = CALCULATE(COUNT(Sheet2[Employee ID ]))

2) No need to create any measure. Just select the Sheet2[Employee ID ] and go to column name and click on "Arrow" and select Count (Distinct).

If you are looking something else please provide more detaila and expected output.

[evandrocunico]

Hi @Anonymous 

No of Employee Selected = DISTINCTCOUNT(Sheet2[Employee ID ])

regards

[Anonymous · Accepted solution]

Hi,

This is not my Expected Output.

                                                No of Employees Selected =

No of Employee Selected = CALCULATE(COUNT(Sheet2[Employee ID ]),FILTER(ALLSELECTED(sheet2),Sheet2[Department]=MAX(Sheet1[Department ID]))

)

This measure having some logics.

Pbix file:

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EbOhY07Tbt1IqrxMmLWum1EB5Vkq...

Thanks in Advance

Yuvaraj

[v-xicai · Accepted solution]

Hi @Anonymous ,

Based on my test, the two methods as @Anonymous offered above work fine.

If you stick to your own formula, you can change it like DAX below.

No of Employee Selected = var d= CALCULATE(COUNT(Sheet2[Employee ID ]),FILTER(ALLSELECTED(sheet2),Sheet2[Department]=MAX(Sheet1[Department ID])))
VAR _table = SUMMARIZE(Sheet1, Sheet1[Department ID],"_Value",d)
RETURN
IF(HASONEVALUE(Sheet1[Department ID]),d,SUMX(_table,[_Value]))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much Amy

[Anonymous]

Hi Amy,

i used the measure which you sent. 

if i click the 05-may-2019 date filter its not working.

Pbix sample file:

https://avacorp1-my.sharepoint.com/:u:/g/personal/yuvaraj_g_avasoft_com/EbOhY07Tbt1IqrxMmLWum1EB5Vkq...

if i click the 05-Jun-2019 Date filter its working

[v-xicai]

Hi @Anonymous ,

You can change the Cross filter direction from Single to Both, which will treat the these tables as a single table .

Best regards

Amy Cai

[Anonymous]

Amy,

I need cross filter direction as single (No of Required Person Measure).

is there is any chance to write a dax code for cross filter as both in a dax query?

Thanks in Advance,

Yuvaraj

[Anonymous]

Amy,

I alter the dax query,

No of Employee Selected = var d= CALCULATE(COUNT(Sheet2[Employee ID ]),FILTER(ALLSELECTED(sheet2),Sheet2[Department]=MAX(Sheet1[Department ID])))

VAR _table = CALCULATE(SUMMARIZE(Sheet1, Sheet1[Department ID],"_Value",d),CROSSFILTER(Sheet1[Department ID],Sheet2[Department],Both)

RETURN

IF(HASONEVALUE(Sheet1[Department ID]),d,SUMX(_table,[_Value]))

Error throws like ,

the syntax for Return for Incorrect.

Any help would be appreciated

Thanks in advance,

Yuvaraj