Forum Discussion

crystalLearn's avatar
crystalLearn
Regular Visitor
4 years ago
Solved

Common item based on the dynamics selection

Hello all, could you pls help. I have tried so long without success, even after I saw a similar case in this forum.

Anonymous  maybe you could explain your idea agin, if it looks the same to you...

amitchandak parry2k I see you guys are also active experts, some idea?

 

Business case: I need to compare the price for the same products from the "adhoc selected suppliers" in the "adhoc selected weeks". Relationship among suppliers : offers to items : CW is M: N: L. And the amount of items is too high to have single selection.

Dataset background: Attached Example is a extreme simplified table. Any creation of a new (calculated) table will cause high performance problem. Hopefully it can be solved only based on certain measures on the visualization level.

 

Depending on the selection (no restriction on the number) of suppliers and CW, the list should show the common article and its price of the offered week. 

No calculation on the price needed yet (it changes every week, even from the same supplier), but only show the price of each supplier for each "Common" product, in order be able to compare the supplier on the "apple for apple" base...

 In order to compare apple with apple, I need only the "Common offered" product to be shown, not all product (in the original data set there are more than thousands of products).

The Supplier and Week should be "selectable" as slicer, so the "filtered" report is based on the selection (in the original data set there are more than 20 suppliers, and the number of weeks is 52, ie. spread over the whole year)

 

I'd love to attach my original powerbi file for your clearer view, but it seems that here only picture can be attached. So here they are: 

result 1data source

Expected result like below: 

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    crystalLearn solution sent you by an email, here is the core measure:

     

    Common Items = 
VAR __itemsWithDistinctSupplier = 
SUMMARIZE ( 
    ALLSELECTED ( Sheet1 ), 
    Sheet1[item], 
    "@C", DISTINCTCOUNT ( Sheet1[supplier] ) 
)
VAR __selectedSupplier = 
    CALCULATE ( 
        DISTINCTCOUNT ( Sheet1[supplier] ), 
        ALLSELECTED ( ) 
    )
VAR __itemsFilter = FILTER ( __itemsWithDistinctSupplier, [@C] = __selectedSupplier )
RETURN 
CALCULATE ( 
    COUNTROWS ( Sheet1 ), 
    KEEPFILTERS ( __itemsFilter ) 
)

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.

5 Replies