Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Raise a flag when user selected ID exists in a path/string

Hi,

I have an items table, and a product table.

In the product table I have a column where all the items required to build the product is listed in A_B_C fashion.

 



So far:
With the help of v-zhangti , so far I have achieved (attached) that if the user selects an ItemID, I can show the list of Products which can be built using this ItemID. 

Target:
User will select a ItemName, and I want to show the user how many Products can be built from it.

Thanks !

Solved: Re: Using PATHCONTAINS and SELECTEDVALUE - Microsoft Power BI Community

Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure = 
Var _N1=SEARCH(SELECTEDVALUE('Item'[ItemName]),SELECTEDVALUE('Product'[Product]),1,0)
Return
IF(_N1>0,1,0)

    Count = COUNTX(FILTER(ALL('Product'),[Measure]<>BLANK()),[Product])

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Based on what I got a new measure

     

    mesasure  =

    var _item = maxx(allselected(Item), Item[Item ID])

    return

    countrows(filter(Product, containsstring(product[Items], _item) ) )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks amitchandak 

      I have few measures based on the user selection, count of products is just one of them.

      I was wondering if I can get a flag in the Products table which will indicate - these are the products I can produce from the user selected Items.

      Then I can create my own measures (count of products, sum of price, earliest date of production) on Product Table.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure = 
Var _N1=SEARCH(SELECTEDVALUE('Item'[ItemName]),SELECTEDVALUE('Product'[Product]),1,0)
Return
IF(_N1>0,1,0)

    Count = COUNTX(FILTER(ALL('Product'),[Measure]<>BLANK()),[Product])

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks v-zhangti  and amitchandak

      The solution will work when I have limited number of measures but I guess anyone would struggle if there are say 20+ measures - as now I have to apply this logic in each and every measure manually.

      Do you think there can be a better way to achieve this ?

      I was thinking about a central filter or flag which can be placed on Report Level, so if the rows satisfies the condititon they are included in all the calcs / measures, otherwise not.



      Thanks