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
denxx34
Frequent Visitor

Total DISTINCTCOUNT() also for BLANKS() with SAMEPERIODLASTYEAR() calculations

Hello,

I need help with the following DAX scenario.

 

Data context: We buy items from vendors.

Question: Comparison of previous year with total number of suppliers.

Problem: Number of suppliers is "wrong" because only the selected year (2023) is calculated [DISTINCTCOUNT()], but I need the DISTINCTCOUNT() for all suppliers. Even if we didn't buy any of these in 2023. I need all of them from the previous year and the year before.

(Description see screenshot)

For 2023 > not #Vendors=3 > #Vendors=5

 

Any ideas on how to overcome this challenge?

Thanks in advance!

 

Data 

 

fact

VendorDateAmountItem
A15.01.202310abc
B15.07.20235abc
C15.08.20233def
D15.01.20226abc
A15.07.20224abc
A15.08.20222def
B20.01.20227abc
E15.01.20216abc
A15.07.20214abc
A15.08.20212def

 

 

dimVendor

VendorVendorName
AAaa
BBbb
CCcc
DDdd
EEee

 

calendar =

FILTER (
    CALENDARAUTO ( 3 ),
    YEAR ( [Date] ) >= 2020
)

 

dimItem

ItemItemName
abcAbeCe
defDeEef

 

 

2024-06-04 09_04_56-#VendorWrong - Power BI Desktop.png

 

2 ACCEPTED SOLUTIONS
Moetazzahran
Resolver II
Resolver II

Hello, 
If I understand your problem correctly, 

You can use two solutions. 
1st solution is to remove the interaction between (The Date slicer and  Fact)and the Vendor KPI card

Moetazzahran_0-1717497711931.pngMoetazzahran_1-1717497749396.png

2nd solution is to use an All() and Calculate in your vendor measure. 

Moetazzahran_2-1717497806865.pngPlease let me know if it works. 

Thank you in advance. 






View solution in original post

@Moetazzahran You're counting all vendors this way, whereas the questioner should be needing the number of vendors based on the number of years less than or equal to the year selected by the slicer.

 

 

@denxx34  You can try below measure.

 

#vendor = CALCULATE(DISTINCT('Fact'[Vendor]),'Calendar'[Date]<=MAX('Calendar'[Date]))

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

 

View solution in original post

2 REPLIES 2
Moetazzahran
Resolver II
Resolver II

Hello, 
If I understand your problem correctly, 

You can use two solutions. 
1st solution is to remove the interaction between (The Date slicer and  Fact)and the Vendor KPI card

Moetazzahran_0-1717497711931.pngMoetazzahran_1-1717497749396.png

2nd solution is to use an All() and Calculate in your vendor measure. 

Moetazzahran_2-1717497806865.pngPlease let me know if it works. 

Thank you in advance. 






@Moetazzahran You're counting all vendors this way, whereas the questioner should be needing the number of vendors based on the number of years less than or equal to the year selected by the slicer.

 

 

@denxx34  You can try below measure.

 

#vendor = CALCULATE(DISTINCT('Fact'[Vendor]),'Calendar'[Date]<=MAX('Calendar'[Date]))

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

 

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.