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
Saxon10
Post Prodigy
Post Prodigy

DISTINCT count and count in-between two tables with two columns

 

 

I have a two tables are Data and Report.

 

Data:

 

Data table contain the following columns are Item and Supplier Status. In this table the same item contain multiple supplier status and the same item unique supplier status as well.

 

Report:

 

In Report table contain supplier status only.

 

Result:

 

I am looking for COUNT and DISTINCT COUNT in same column according to the supplier status in Report Table.

 

I would like to achieve my desired result by using DAX (New calculated column)

 

I am applying the following DAXCOUNT FOR SUPPLIER = CALCULATE(COUNT('DATA'[Supplier Status]),FILTER(ALL('DATA'),'DATA'[Supplier Status]=EARLIER(REPORT[SUPPLIER STATUS])))” in Report table but it give a wrong result were supplier status is “MIXED” and rest of them is fine.

 

Any advice please.

 

Data Table:

 

ITEM   SUPPLIER STATUS

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

123AA MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

234     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

534     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

543     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

545     MULTIPLE

123     A85

124     A86

125     A91

126     A87

127     A92

128     A93

129     A94

130     A85

131     A86

132     A91

133     A87

134     A92

135     A93

136     A94

 

Report Table:

 

SUPPLIER STATUS    COUNT FOR SUPPLIER (DESIRED RESULT)

A85     5

A86     6

A87     8

A91     8

A92     10

A93     11

A94     10

MULTIPLE       5

 

Herewith attached the PBI file for your reference https://www.dropbox.com/s/x0pe0i761saqqek/DISCOUNT%20and%20COUNT%20in%20same%20column%20in-between%2...

 

Desired Result Snapshot:

 

Capture.PNG

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@Saxon10 solution attached. tweak it as per your need.

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@Saxon10 solution attached. tweak it as per your need.

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you so much for your help and advise. Your solution working well. 

 

If it possbile can you please provide any alternative solution? instated off releatedtable? It's very hard to understand the releated function for beginners. Sometimes the both tables not automatically detected so need to be add the releationship.

 

COUNT FOR SUPPLIER = CALCULATE ( DISTINCTCOUNT ( DATA[ITEM] ), RELATEDTABLE ( DATA ) )

 Please advise.

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.