Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Bijal Patel]

Based on the below data, I want the report to show:

• FILTER 1: where the count of products > 1 for the regions  
• FILTER 2: also show product “DDD” (even though the count is 1 as it is just in 1 region)

ProductID	Name	Region	Sales
AAA	Product A	US	10,000
BBB	Product B	CAN	20,000
BBB	Product B	US	30,000
CCC	Product C	US	40,000
DDD	Product D	EU	15,000
AAA	product A	EU	10,000
AAA	Product A	CAN	10,000
EEE	Product E	US	10,000

So the report should show:

Products	Count
AAA	3
BBB	2
DDD	1

 

Also, the second filter can change from time to time, so would like the user to have the ability to include / exclude products as needed. For example: currently, they have included “DDD” but they might want to include “XYZ” later.

I tried adding the count in the filter panel where COUNT is Greater than 1 . This works but does not show “DDD” . 

How can I achieve this kind of filtering?

——————————
Bijal Patel
——————————

[Lutz Bendlin]

In your measure definition you can use logic operators like “&&”  (and)  and “||”  (or) to model these conditions.  There’s also the OR() DAX function.

——————————
Lutz
——————————
——————————————-

[Bijal Patel]

Thanks Lutz! I am new to Power BI and I can’t get to code the below logic in a measure. 

Can you provide me with a sample measure for the below condition? 

If count(product) > 1
          display count
    else
          if  product = “DDD”          
                 display  count 
          end if
end if

——————————
Bijal Patel
——————————
——————————————-

[Lutz Bendlin]

As always there are many ways to do that.

The option with logic operators:
Var x = if(count(product)>1 || product =”DDD”,count,blank())

If you don’t want to to write nested IF statements you can use the syntax sugar SWITCH():
Var x = switch(true(),count(product)>1,count,product = “DDD”,count,blank())

etc.

——————————
Lutz
——————————
——————————————-

[Bijal Patel]

Thank you so much Lutz!

I was able to achieve what I wanted by:
1) creating a measure to return the count of the products that I wanted to include (in this case “DDD”) – had to do this as I wasn’t able to reference the column in the IF condition in the measure directly. So couldn’t do: product = “DDD”. 
2) Then created a measure with the IF condition : where count(product)> 1 || [Measure created in Step 1]

May be you can combine both the measures but in my case had to keep them separate as I plan on using the first measure in other parts of report.

thanks once again for your time and help!

——————————
Bijal Patel
——————————
——————————————-