Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated measure not showing sum value in a table

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...
Screenshot_10.png

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi,

 

This is not my Expected Output.

Screenshot_10.png                                                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:
 
 
Thanks in Advance
Yuvaraj

View solution in original post

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]))

 

4.png

 

 

 

 

 

Best Regards,

Amy

 

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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
Resolver III
Resolver III

Hi @Anonymous 

 

No of Employee Selected = DISTINCTCOUNT(Sheet2[Employee ID ])
 
regards
Anonymous
Not applicable

Hi,

 

This is not my Expected Output.

Screenshot_10.png                                                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:
 
 
Thanks in Advance
Yuvaraj

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]))

 

4.png

 

 

 

 

 

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
Not applicable

Thank you so much Amy

Anonymous
Not applicable

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

Screenshot_12.pngif i click the 05-Jun-2019 Date filter its working

Screenshot_11.png

Hi @Anonymous ,

 

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

 

6.png

 

 

 

 

 

 

 

 

 

 

 

Best regards

Amy Cai

Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.