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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
iamprajot
Responsive Resident
Responsive Resident

How to write this Condition in Power BI

I need the Name of the Product from TableA for which I have recieved most Quotes.

for e.g. Select Name of Product where Max ( Count (Quotes ) )

 

Also the Name of the Product from TableA for which I have recieved most Quotes and for which ... (Another Condition).

for e.g. Select Name of Product where Max ( Count (Quotes ) ) and where Count (Quotes ) >100

1 ACCEPTED SOLUTION
iamprajot
Responsive Resident
Responsive Resident

It is working and I am accepting it as a solution however if you could tell me any other simpler way then it would be great as it seems a lot confusing.

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

Perhaps something along the lines of:

 

MyMax = CALCULATE(MAX(Table([Product]),FILTER(Table,MAXX(Table,COUNT([Quotes])))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

This is exactly what I am using and it seems very feasible but it is not giving desired results always.

I am using slicers/filters and it ives correct results for some and incorrect result for the rest.

I don't know on what basis it is giving rubbish results.

Could be that you need to add an ALL or ALLEXCEPT clause or something along those lines. Can you post some sample data and description of where you are seeing the issue?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

You know how the world works, I can't share the exact data but sure I will replicate the thing from Northwind Database and share soon.

https://1drv.ms/u/s!AmsIJlc64zz0iSGPhQZbprlPgkH_

Here is the link to PBI file.

I have 3 columns COUNTRY, ProductID, STATUS

I want the COUNTRY which is Repeated the Most in First CARD

I want the ProductID which is Repeated the Most and with the STATUS=TRUE (2nd condition) in Second CARD

and both should work correct with the filter/slicer above.

 

Currrently it is giving wrong results.

If you wish to take into account that you might have ties in your data (as in the sample file) you could use the following two measures in your file.

I have tested them and they produce correct result.

 

Most frequent country =
CONCATENATEX(
 CALCULATETABLE(
  VALUES(Data[COUNTRY]);
  TOPN(1;VALUES(Data[COUNTRY]);CALCULATE(COUNTROWS('Data'));DESC)
 );
 'Data'[COUNTRY];", "
)

 

Most freq product with status true =
CONCATENATEX(
 CALCULATETABLE(
  VALUES(Data[PRODUCTID]);
  TOPN(1;VALUES(Data[PRODUCTID]);CALCULATE(COUNTROWS('Data'));DESC);
  'Data'[STATUS] = TRUE()
 );
 'Data'[PRODUCTID];", "
)

 

Br,

Magnus

iamprajot
Responsive Resident
Responsive Resident

It is working and I am accepting it as a solution however if you could tell me any other simpler way then it would be great as it seems a lot confusing.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.