Hierarchy dax measure returning wrong results

Stubby

Board Regular
Joined
Mar 5, 2002
Messages
188
Hi. I'm currently having an issue with a Power BI measure returning the wrong answer when using a flattened hierarchy. Here's the situation!

I have a data table that has a list of courses that staff are expected to complete. I need to create a visual that my manager can drill down and see which member of staff is Overdue.

example 3.jpg

I have created the following measure that counts if a particular course is showing as overdue

RD GoodDoc1 =
VAR CountGoodDocOver= CALCULATE(
DISTINCTCOUNT(ReqLearning[User ID]),
(FILTER(ReqLearning,ReqLearning[Item ID]="rd-all-gooddoc1" && ReqLearning[Completion Status]="Overdue")))
RETURN
if(ISBLANK(CountGoodDocOver),0,CountGoodDocOver)

the problem i have is that if i plot the measure on a graph together with the flattened hierarchy i am getting the wrong results. the chart below shows that Priya has 1 "All Good" overdue but the data shown in the above table shows thats incorrect. Strangly enough if i plot a matrix using just a complete list of staff it does show correctly.

2nd example.jpg

Any help on this will be greatly appreciated as its driving me crazy
 

Attachments

  • query.jpg
    query.jpg
    29.8 KB · Views: 2

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

anvg

Active Member
Joined
Feb 14, 2012
Messages
485
the chart below shows that Priya has 1 "All Good" overdue but the data shown in the above table shows thats incorrect.
Hi.
Why? Your table contains a single row for User ID = 162770 where Item ID = "rd-all-gooddoc1" (row number 2 in the table). What is incorrect?
Regards,
 

Stubby

Board Regular
Joined
Mar 5, 2002
Messages
188
The Table shows the status of Complete, whereas the measure should be looking for rows that has the status of Overdue.

However since posting this, i think i may have discovered a problem within the data itself!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,059
Messages
5,639,816
Members
417,116
Latest member
Jakeyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top