Parameter depending on another parameter

[Anonymous]

Hi all,

I have created a table with the values "amount" and "net worth"

Using the switch() function, I created measures in order to get the right values for the sales, procurement and the storage. 

Trend sales = SWITCH(SELECTEDVALUE('table1'[Column2]),

"Amount, SUM('[Sales'[amount]),

"Net worth", SUM('Sales'[net worth])

Trend procurement = SWITCH(SELECTEDVALUE('table1'[Column2]),

"Amount, SUM('[Procurement'[amount]),

"Net worth", SUM('Procurement'[net worth])

Trend storage = SWITCH(SELECTEDVALUE('table1'[Column2]),

"Amount, SUM('[Storage'[amount]),

"Net worth", SUM('Storage'[net worth])

Then I created a parameter:

Parameter = {

    ("Sales", NAMEOF('Measure table'[Trend sales]), 0),

    ("Procurement", NAMEOF('Measure table'[Trend procurement),1),

    ("Storage", NAMEOF('Measure table'[Trend storage]),2)

}

I have red and black products

However, I want a second parameter in order to select all the products, the red products and the black products. I have a column which specifies which products are red and which are black. 

I want to use this to see the total sales/purchases/storage, but also make it possible to compare the red and black products in the same visual. 

I can't manage to create a second parameter, can someone help me?? Thank you in advance!

[Anonymous · Accepted solution]

Hi, @Anonymous 

Based on the DAX expression you showed, I create the following tables:

First, I created a metric using the following DAX expression:

SelectedProducts = 
SWITCH(
    SELECTEDVALUE('Table'[ProductType]),
    "Red", "Red",
    "Black","Black"
)

Modify the base of your DAX expression to:

Trend procurement = CALCULATE(SWITCH(SELECTEDVALUE('table1'[Column2]),
"Amount", SUM('Procurement'[Amount]),
"Net worth", SUM('Procurement'[net worth])
),
FILTER('Procurement','Procurement'[Color] IN SUMMARIZE('Table',[ProductType]))
)

Trend sales = 
CALCULATE(SWITCH(SELECTEDVALUE('table1'[Column2]),
"Amount", SUM(Sales[Amount]),
"Net worth", SUM('Sales'[net worth])
),
FILTER('Sales','Sales'[Color] IN SUMMARIZE('Table',[ProductType]))
)



Trend storage = CALCULATE(SWITCH(SELECTEDVALUE('table1'[Column2]),
"Amount", SUM('Storage'[Amount]),
"Net worth", SUM('Storage'[net worth])
),FILTER('Storage','Storage'[Color] IN SUMMARIZE('Table',[ProductType]))
)

Create a slicer using ProductType:

The results are as follows:
When I select Red, it automatically adjusts the three metrics to calculate the AMOUNT of the corresponding table where the color column is Red:

If you want to contrast red and black in the same table visual object, this is currently not very easy to achieve. If you want to achieve this then you can create new Trend sales1, Trend storage2, Trend procurement3 metrics and change the sum in them to CALCULATE(SUM('Procurement'[Amount]),'Procurement'[Color]= “Red")
This will sum one of the colors and then add it to your parameter slicer, select the other color in the Product Type slicer and you will be able to see how the different colors compare.

Best Regards

Jianpeng Li

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