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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Fxelixe
Frequent Visitor

Re:

Hi

 

So i have trouble to create logic to solve package issue.

the goal is to put remark when customer buy our package which consist of several products

 

if customer didn't buy the specific product then we remark it as 0, if buy all of the specific products then we remark it as 1

Here's my DAX logic 

Package Remark = CALCULATE([Unit Sold],[Unit Sold]>=1.0,filter('Product',[Product Name] IN {"Product A","Product B","Product C","Product D"}))

Here's some excel visualization for the expected output
Fxelixe_0-1671358094536.png


Well in excel i could easily do COUNT IF cell value >= 1.5 is equal to 6 then 1 , if false 0. then sum it. 
but i cannot do that easily in power BI. still learning to be better.

Thank you

3 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

It is for creating a measure.

I hope the below can provide some ideas on how to create a solution for your datamodel.

 

Jihwan_Kim_0-1671366869137.png

 

Jihwan_Kim_1-1671366880396.png

 

Expected output: =
VAR _productAcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product A" ) <> 0
VAR _productBcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product B" ) <> 0
VAR _productCcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product C" ) <> 0
VAR _productDcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product D" ) <> 0
RETURN
    IF (
        HASONEVALUE ( User[User] ),
        _productAcondition * _productBcondition * _productCcondition * _productDcondition
    )

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

Fxelixe
Frequent Visitor

Update:

Already have the answer but kinda complicated.

the idea is to create multiple var to count the products
and then return it using floor
return
floor(item(1)+item(2)+....+Item(n),n)/n


View solution in original post

Hi,

Thank you for your message, and please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

 

Jihwan_Kim_0-1673752762909.png

 

Jihwan_Kim_1-1673754017607.png

 

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

Hi @Fxelixe 

please try

Package Remark =
VAR Count1 =
COUNTROWS (
FILTER (
VALUES ( 'Product'[Product Name] ),
'Product'[Product Name]
IN { "Product A", "Product B", "Product C", "Product D" }
)
)
RETURN
IF ( ISINSCOPE ( 'Product'[Product Name] ), Count1, IF ( Count1 = 4, 1 ) )

Fxelixe
Frequent Visitor

Update:

Already have the answer but kinda complicated.

the idea is to create multiple var to count the products
and then return it using floor
return
floor(item(1)+item(2)+....+Item(n),n)/n


Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

It is for creating a measure.

I hope the below can provide some ideas on how to create a solution for your datamodel.

 

Jihwan_Kim_0-1671366869137.png

 

Jihwan_Kim_1-1671366880396.png

 

Expected output: =
VAR _productAcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product A" ) <> 0
VAR _productBcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product B" ) <> 0
VAR _productCcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product C" ) <> 0
VAR _productDcondition =
    CALCULATE ( SUM ( Data[Quantity] ), Package[Product Name] = "Product D" ) <> 0
RETURN
    IF (
        HASONEVALUE ( User[User] ),
        _productAcondition * _productBcondition * _productCcondition * _productDcondition
    )

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hi @Jihwan_Kim ,

your dax works and helps me a lot!, but suddenly the problem got more complex for me:

1.  package is valid if each product's sold is >= 1.5
2.  the result can be cumulative on the last solution it would be total = 2   
(my hypothesis because if we use IF statement, the calculation will treat them as logical, not cumulative)
3.  each user has one group and the result expected to be summarize by group

Here's the updated table

Fxelixe_0-1673726033854.png


and then if grouped by "Group"

Fxelixe_1-1673726075682.png

thank you so much

Hi,

Thank you for your message, and please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

 

Jihwan_Kim_0-1673752762909.png

 

Jihwan_Kim_1-1673754017607.png

 

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Thank you so much for helping me getting be better on dax. really appreciate it

yes i also think the same idea hahaha. thank you, i hope in the future someone will be helped by this thread

FreemanZ
Super User
Super User

hi @Fxelixe 

IN is OR logic, but it seems you expect a AND logic. Try to modify it firstly.

 

If the issue persists, it would be necessary to also provide more info about your data model: tables, columns and relationships. It seems you have at least a Product Table and Fact Table, maybe a Customer Table as well. 

yes I already think to use AND, but AND logic only works for 2 item. I need flexibilities to determine # of products

you can use && to combine Boolean expressions as many as possible.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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