Dynamic measure based on slicer selection

[Anonymous]

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

Name	Product
Peter	Product_A
Peter	Product_B
Peter	Product_C
Peter	Product_D
Sam	Product_B
Sam	Product_C
Sam	Product_E
Lisa	Product_C
Lisa	Product_D
Lisa	Product_B
Chloe	Product_B
Andrew	Product_F
Mike	Product_B
Mike	Product_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

Name	Products	Count
Andrew	Product_F	1
Chloe	Product_B	Complete
Lisa	Product_C|Product_D|Product_B	1
Mike	Product_B|Product_C	Complete
Peter	Product_A|Product_B|Product_C|Product_D	2
Sam	Product_B|Product_C|Product_E	1

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

Thank you.

[TomMartens · Accepted solution]

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]

Hi TomMartens

Thank you so much for your help, it is exactly what I am after. Just a couple of questions on your method for me to understand a bit on your approach if you don't mind.

 - 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]

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

[Anonymous]

Hi TomMartens, thank you so much for the explanation.

Just double checking with the new measures you got there. After I established relationship between 'Table1' and 'Product' table, the [Product Count] measure is acting is little bit strange as below screenshots when I selected just "Product_F" on the slicer. However, when the relationship is deleted between 'Table1' and 'Product' becoming unrelated, this measure then works fine. Just wondering why it is happening? Thanks