how to create a Pivot table based on the selected values from another table? - Please help

[Anonymous]

Hi,

I have created a demo app and attaching the link. I want that when I select the customer ID from table1, it will show realted data in below 2nd pivot table. For this I have created a Pivot table with column name and Values. But it does not work.

https://drive.google.com/file/d/1Qk8mfDfUBINNUC2S0FHbgGb8JJmInFv5/view?usp=sharing

So please help me how to achive this functionality?

[v-lili6-msft]

hi, @Anonymous 

First, you should know that calculate column and calculate table can't be affected by any slicer. 
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for measure is an aggregate value, and what you want is a pivot table, you may try this way:

Step1:

In Edit Queries, Duplicate the order table, and for duplicate table remove all the columns expect(Row ID, Customer ID, Product ID) like below:

Step2:

Select Customer ID, Product ID then unpovit them.

Step3:

Close& Apply and then create a relationship between oders and duplicate table by Row ID column

Step4:

Now, drag duplicate table Attribute and Value into a table visual.

here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi,

Can I use measure in Calculated Pivot table?means if I have created a measure and used it in a Calculated Pivot table like below?

Pivot Table = UNION(

SELECTCOLUMNS (

Data,

"Column Name", "Alert Category",

"Values", Data[Alert Cat measure]

),

SELECTCOLUMNS (

Data,

"Column Name", "File ID",

"Values", Data[FILE_ID_SELECTED]

)

)

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

No, that is just a formula in Calculated Pivot table expression.

In simple terms, this expression is a "calculate column formula" not a "calculate measure formula" for current context of the formula that is used to create a calculate table. 

Note: calculate column and calculate table can't be affected by any slicer.

Best Regards,

Lin