Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic measure based on slicer selection

Hi Everyone,

 

I was hoping you'd be able to assist with a problem I'm having in Power BI where I would like to create a measure based on slicer slection.

Suppose my data is below

NameProduct
PeterProduct_A
PeterProduct_B
PeterProduct_C
PeterProduct_D
SamProduct_B
SamProduct_C
SamProduct_E
LisaProduct_C
LisaProduct_D
LisaProduct_B
ChloeProduct_B
AndrewProduct_F
MikeProduct_B
MikeProduct_C

 

I would like to create a slicer of [Product] and a Table visual, where the Table is to display list of [Name] in the first column and the associated [Product] concatenated in the second column. These 2 columns were simple enough to create as I used some power query transformation. However, I also wish create a third column with a measure displaying the count of products under each [Name] that changes with slicer selection. I would like the product count to reduce when an instance of [Product] is selected on slicer until "Complete" when ALL associate products are selected in each [Name]. 

The desired result when "Product_B" and "Product_C" are selected in the slicer is below

NameProductsCount
AndrewProduct_F1
ChloeProduct_BComplete
LisaProduct_C|Product_D|Product_B1
MikeProduct_B|Product_CComplete
PeterProduct_A|Product_B|Product_C|Product_D2
SamProduct_B|Product_C|Product_E1

 

I'm wondering if my desired result is possible with Power BI and DAX?

 

Thank you.

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

     

    first I created a table that just contains the Products but is not related to the other table. I used this DAX statement to created this table:

     

    Product Unrelated = 
DISTINCT('Table1'[Product])

     

    I used this table to populate the slicer.

    I also created this measure that "recreates" the concatenated products per Name:

     

    Products Concatenated = 
IF(HASONEVALUE(Table1[Name])
    , CONCATENATEX(
        VALUES(Table1[Product])
        , [Product]
        , "|"
        , [Product]
        , ASC
    )
)

    The following measure counts the Products that are not selected in the slicer:

     

    Products Count = 
var ProductsFromSlicer = VALUES('Product Unrelated'[Product])
return
IF(HASONEVALUE('Table1'[Name])
    , var presentProducuts = VALUES('Table1'[Product])
    var numberOfExistingProducts = COUNTROWS(EXCEPT(presentProducuts,ProductsFromSlicer))
    return
    IF(ISBLANK(numberOfExistingProducts), "Complete", numberOfExistingProducts)
)

    All this allows me to create this report:

     

    Hopefully this provides what you are looking for.

     

    Regards,

    Tom

     

     

     

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    7 years ago

    Hey,

     

    first I created a table that just contains the Products but is not related to the other table. I used this DAX statement to created this table:

     

    Product Unrelated = 
DISTINCT('Table1'[Product])

     

    I used this table to populate the slicer.

    I also created this measure that "recreates" the concatenated products per Name:

     

    Products Concatenated = 
IF(HASONEVALUE(Table1[Name])
    , CONCATENATEX(
        VALUES(Table1[Product])
        , [Product]
        , "|"
        , [Product]
        , ASC
    )
)

    The following measure counts the Products that are not selected in the slicer:

     

    Products Count = 
var ProductsFromSlicer = VALUES('Product Unrelated'[Product])
return
IF(HASONEVALUE('Table1'[Name])
    , var presentProducuts = VALUES('Table1'[Product])
    var numberOfExistingProducts = COUNTROWS(EXCEPT(presentProducuts,ProductsFromSlicer))
    return
    IF(ISBLANK(numberOfExistingProducts), "Complete", numberOfExistingProducts)
)

    All this allows me to create this report:

     

    Hopefully this provides what you are looking for.

     

    Regards,

    Tom

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi 

       - Could you explain why you chose to create a separate unrelated table and not use simply use the existing product list in same table?

       - Could you explain a little on how you created the "Product Count" measure and how it is working? I cannot seem to wrap my head around it.

       

      Lastly, if the slicer is left unselected the "product count" column is defaulted has "Complete", are we able to make it default as count of remaining product? Thanks again.

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        7 years ago

        Hey,

        I tweaked the model a little bit:

        Now the extra table is called "Product" and is related.

        A separate table is necessary due to the fact depending on the data distribution in the main table a product might not be considered. The problem that comes with one-table solutions is related to the concept of auto-exists, this concept is esplained in this article https://www.sqlbi.com/articles/understanding-dax-auto-exist/ 

        The idea of the unrelated table was simply this, the measurs will be more simple :-)

         

        I changed the measure "Products Concatenated" to this:

        Products Concatenated = 
IF(HASONEVALUE(Table1[Name])
    , CALCULATE(
        CONCATENATEX(
            VALUES(Table1[Product])
            , [Product]
            , "|"
            , [Product]
            , ASC
        )
        ,ALL('Product'[Product])
)
)

        and the measure that counts the missing products (considering the slicer selections) to this:

        Products Count = 
var ProductsFromSlicer = VALUES('Product'[Product])
var _IsFiltered = ISFILTERED('Product'[Product])
return
CALCULATE(
IF(HASONEVALUE('Table1'[Name])
    , var presentProducuts = VALUES('Table1'[Product])
    var numberOfExistingProducts = COUNTROWS(EXCEPT(presentProducuts,ProductsFromSlicer))
    return
    IF( _IsFiltered
        , IF(ISBLANK(numberOfExistingProducts), "Complete", numberOfExistingProducts)
        , COUNTROWS(presentProducuts)
    )
)
,ALL('Product'[Product])
)

        Now, there is a check if a selection has been made in the slicer using the function ISFILTERED(...), if this is not the case the false path of  IF ( _IsFiltered ... 
        is selected and the number of present products is returned.
        It's necessary to modify the filter using ALL('Product'[Product]) to make sure that all the products associated with the current name will be considered.

        Basically the count of missing products works like this:

        • values('product'[product]) returns a table with all the products, basically no selection also returns all product, for this reason the additional check _isfiltered has been introduced
        • values('table1'[Product] returns all the assigned products, it's necessary to use ALL('Product'[Product])
        • the number of rows are counted that are present, but not selected COUNTROWS(EXCEPT(presentProducts , ProductsFromSlicer))

        Hopefully this explains my thinking, now if nothing is selected from the slicer, the report looks like this:

        And if Product B and C are selected:

         

        Regards,

        Tom