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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jonnyA
Responsive Resident
Responsive Resident

creating a measure that brings back a "count Distinct Voucher #" that will only filter in "Quarter 1

Hello,

 

Hoping someone could help me out with a measure Ive been trying to create.

 

I'm basically trying to create a new measure using the Field "Voucher Number" which will be "Count Distinct", but only bring back Quarter 1, and then Quarters 2, 3 and 4 (I think i'll be able to figure it out the other 3 once I figure out the quarter 1)

 

I can do this by using the Visualation "Card", by using the "Filters on the visual" ... hopefully the screen grab will help you see what I did ...

 

The name of the data set is "123_Charge_Data_Set" ... I'm using field "Voucher_Number" ... and a DOS - Quarter filter ...

jonnyA_0-1614286963407.png

 

Thanks for the help in advance 🙂

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi, @jonnyA 

It should be caused by different data types used for comparison. You can try the following Measure.

 

Vouchers Qtr 1 =

CALCULATE (

    DISTINCTCOUNT ( Charge_Dataset[Voucher_Number] ),

    FILTER ( 'Charge_Dataset', Quarter(Charge_Dataset[DOS])= 1 )

)

 

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? 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

13 REPLIES 13
jonnyA
Responsive Resident
Responsive Resident

@v-cazheng-msft , if interested in solving, i having another issue related to the measure.  I cant get my total to sum correctly for Total Patients.  The total should be 4075, but Power BI is showing 4073.

Here are some of the measurse I am using ...

I tweaked the formula you gave me to count Patients.

Patients = DISTINCTCOUNT(Charge_Dataset[Patient_Name])
Here is the formula I created similar to the one you sent me ...
Patients Qtr 1 =
CALCULATE (
( Charge_Dataset[Patients] ),
FILTER ( '_Charge_Dataset', Quarter(_Charge_Dataset[DOS])= 1 ))
So I created 3 more measures similar to the one above twaeking the quarter, so the Quarter 2 measure looked like this ...
Patients Qtr 2 =
CALCULATE (
( _Charge_Dataset[Patients] ),
FILTER ( 'Charge_Dataset', Quarter(GNP_Charge_Dataset[DOS])= 2 ))
Then I did the same thing for Quarter 3 and Quarter 4
Then I created a quick measure that added Quarter 1 and Quarter 2
Then I created another measure that added Quarter 3 and Quarter 4
Finally, I crteated a measure the added the 2 abpve together which looks like this ...
Total Patients =
[Patients Qtr 1 plus Patients Qtr 2] + [Patients Qtr 3 plus Patients Qtr 4]+0
But as you can see form my screenshot the Total is off by 2.
Any chance you know why I'm off there?

PBI 3.8.21.PNG

 
 

 

 

 

jonnyA
Responsive Resident
Responsive Resident

@v-cazheng-msft , thank you for your time and effort with this issue.  You last solution worked, high five!

v-cazheng-msft
Community Support
Community Support

Hi, @jonnyA 

You can try a Measure like the following.

 

Vouchers Qtr 1 =

CALCULATE (

    DISTINCTCOUNT ( Charge_Dataset[Voucher_Number] ),

    FILTER ( 'Charge_Dataset', Charge_Dataset[DOS].[Quarter] = "Qtr 1" )

)

 

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-cazheng-msft 

 

Your formula is very close, but when I do it on my end the formula is not liking where you have put "[Quarter]"

Any advice?

Thank you!

jonnyA_0-1614772636619.pngjonnyA_1-1614772678792.png

 

Hi, @jonnyA 

You can replace it with the QUARTER function.

 

Vouchers Qtr 1 =

CALCULATE (

    DISTINCTCOUNT ( Charge_Dataset[Voucher_Number] ),

    FILTER ( 'Charge_Dataset', Quarter(Charge_Dataset[DOS])= "Qtr 1" )

)

 

Best Regards,

Caiyun Zheng

@v-cazheng-msft , thank you for your time and effort with my issue.  For whatever reason, Power BI is not liking that formula.  I couldnt get it to work.

Hi, @jonnyA 

It should be caused by different data types used for comparison. You can try the following Measure.

 

Vouchers Qtr 1 =

CALCULATE (

    DISTINCTCOUNT ( Charge_Dataset[Voucher_Number] ),

    FILTER ( 'Charge_Dataset', Quarter(Charge_Dataset[DOS])= 1 )

)

 

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

d_gosbell
Super User
Super User

Sorry, but I cannot really see what is under that yellow appart from the red squiggles. Do you maybe have some extra spaces in there? I did a quick test with what I believe is a similar structure and it worked fine.

 

d_gosbell_0-1614346429142.png

 

jonnyA
Responsive Resident
Responsive Resident

@d_gosbell 

 

Here it is again.  I dont believe there are any unwanted spaces.  Is the "." between [DOS] and [Quarter] correct?  Im stumped?  Do you have any other ideas?

 

jonnyA_0-1614346861320.png

 

jonnyA
Responsive Resident
Responsive Resident

@d_gosbell 

I dont know why my previous messages aren't tagging you?

 

Seeing if this works.

jonnyA
Responsive Resident
Responsive Resident

@d_gosbell , thank you for your respone, much appreciation 🙂

 

However, the measure is not liking something as you can see the red squigglies under [Quarter].

 

I circled the DOS Date Hierarchy.

 

Thanks in advance for the help!

 

jonnyA_0-1614343902886.png

 

d_gosbell
Super User
Super User

If I'm understanding your requirements correctly you should be able to create a measure something like the following to create a measure that always returns the Qtr 1 value

 

Vouchers Qtr 1 = CALCULATE(
    DISTINCTCOUNT( '123_Charge_Data_Set'[Voucher_Number] ),
    '123_Charge_Data_Set'[DOS].[Quarter] = "Qtr 1"
)

jonnyA
Responsive Resident
Responsive Resident

 @d_gosbell , thank you for your respone, much appreciation 🙂

However, the measure is not liking something as you can see the red squigglies under [Quarter].

I circled the DOS Date Hierarchy.

Thanks in advance for the help!

PBI 2.26.21.PNG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors