if/switch condition to change the table column value on the click of column in a stackedcolumn chart

[Anonymous]

Hi,

I am new to powerbi and learning. Need ideas and help on the below 2 points: 

1) if on the click of LoDate column (i.e. October 2017) only in a Line and stacked column chart ( refer  1) in the screenshot highlighted in blue) looking for a  if/switch condition to change the table column "c/100" value with other column "C" value ( refer  2) in the screenshot highlighted in blue ) 

2) Restrict the user from selecting more than one LoDate column in a Line and stacked column chart   (or ) display a message saying "select one LoDate column ".

sample code for reference.

Measure Selection =
IF(NOT(COUNTROWS('Measure Dimensions')>1);
SWITCH( TRUE();
VALUES('Measure Dimensions'[Measure]) = "Total Sales"; [Total Sales];
VALUES('Measure Dimensions'[Measure]) = "Total Costs"; [Total Costs];
VALUES('Measure Dimensions'[Measure]) = "Total Profits"; [Total Profits];
BLANK())
;
"Select one measure")

Thanks.

[Greg_Deckler · Accepted solution]

Sample data would be great to play around with this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, that being said, you would want a measure that does a HASONEVALUE check against the column you have in your x-axis in your column chart and if so, divide by 100, otherwise not.

[Greg_Deckler · Accepted solution]

Sample data would be great to play around with this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, that being said, you would want a measure that does a HASONEVALUE check against the column you have in your x-axis in your column chart and if so, divide by 100, otherwise not.

[Anonymous]

Hi,

Please refer the below link to download the sample report pbix

https://www.dropbox.com/s/2io37811qat95b8/sample%20report.pbix?dl=0

Current Behavior:

If i select any row in product_data table... table 2 table is displaying with the respective data of that selected quote and version.

followed below steps to achieve the requirement:

Created a copy from product table to achieve the requirement.

Steps:

1. Create new table based on product table and use new table to build visual.

Table formula:

Table = product_data

2. Write measure to get selected value from new table, then compare table 2 row contents with above value to return tag.

Measure:

Tag =
IF (
      MAX ( addon_data[version] ) IN ALLSELECTED ( 'Table'[version] )
     && MAX ( addon_data[quoteNo] ) IN ALLSELECTED ( 'Table'[quoteNo] ),
     1,
     0
   )

3. Drag tag measure to table 2 visual level filter.

i want to add below condition to the measure:

If i select any row in product_data table... table 2 table is displaying with the respective data of that selected quote and version. Along with that, I also need the proposalId column value in table 2 to be replaced with proposalNewId column value (i.e. 12 ). fyi... proposalNewId column is in addon_data table.

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

From your description, you could refer to below step:

Create a measure:

Filter value = IF(ISFILTERED('product_data'[id]),SWITCH(CALCULATE(SUM(addon_data[proposalId])),MAX('addon_data'[proposalId]),MAX(addon_data[proposalNewId])),CALCULATE(SUM('addon_data'[proposalId])))

Add the measure into the table 2 and remove the [proposalID] from the table 2.

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/uodu03bb1upqglg/if%20switch%20condition%20to%20change%20the%20table%20column%20value%20on%20the%20click%20of%20column%20in%20a%20stackedcolumn.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Thank you for the solution Daniel. I accept your solution.

I have tried to incorporate the same measure logic in  the power bi report  i am working  on but seems to be the measure is not working.  The issue is   the Filter value always displays "SinglePartWarrantyRate" column value and  switch condition  is not working.  please correct my mistake .  

Measure I used:

Filter = IF(
ISFILTERED('VehicleTrend'[LoDate]),SWITCH(CALCULATE(SUM(VehicleTopIssue[C/100])),MAX('VehicleTopIssue'[C/100]),MAX(VehicleTopIssue[SinglePartWarrantyRate])),CALCULATE(SUM('VehicleTopIssue'[C/100]))
)

I have 2 columns  "C/100"   and    "SinglePartWarrantyRate" in table. these columns are decimal(11,10) datatypes. refer the screenshot.

1) if user selects the LoDate column (i.e. August 2017)  in a Line and stacked column chart ( refer  the screenshot  ) the filter measure should display the "SinglePartWarrantyRate" column value.

2) If  User unselects theLoDate column (i.e. August 2017)  in a Line and stacked column chart then the expected behavior should be   Filter measure value  should display the "C/100" column value.  By default   Filter measure value  should display the "C/100" column value.

[Anonymous]

Thanks for the reply. 

I Want a measure that does a HASONEVALUE check against the column you have in your x-axis in your column chart and if so, replace the top issue table column 'c/100'  with  column 'C' . Please refer the screenshot attached in the initiall post.

I tried something like below creating a measure but this measure is not working: 

Measure = IF
(
MAX ( VehicleTrend[LoDate] ) IN ALLSELECTED ( 'VehicleTopIssue'[LoDate] ),
'VehicleTopIssue'[C],'VehicleTopIssue'[C/100]
)

[Anonymous]

Hi,

Please refer the below link to download the sample report pbix 

https://www.dropbox.com/s/2io37811qat95b8/sample%20report.pbix?dl=0

Current Behavior:

If i select any row in product_data table... table 2 table is displaying with the respective data of that selected quote and version.

followed below steps to 

Created a copy from product table to achieve the requirement.

Steps:

1. Create new table based on product table and use new table to build visual.

Table formula:

Table = product_data

2. Write measure to get selected value from new table, then compare table 2 row contents with above value to return tag.

 Measure:

 Tag =
IF (
      MAX ( addon_data[version] ) IN ALLSELECTED ( 'Table'[version] )
     && MAX ( addon_data[quoteNo] ) IN ALLSELECTED ( 'Table'[quoteNo] ),
     1,
    0
)

3. Drag tag measure to table 2 visual level filter. 

 i want to add below condition to the measure:

If i select any row in product_data table... table 2 table is displaying with the respective data of that selected quote and version.  Along with that, I also need the proposalId column value in table 2 to be replaced with proposalNewId column value (i.e. 12 ). fyi... proposalNewId column is in addon_data table.

[Anonymous]

Hi,

please refer to the below link  to download the attached sample report pbix.

https://www.dropbox.com/s/2io37811qat95b8/sample%20report.pbix?dl=0

Current Behavior:

If i select any row in product_data table... table 2 table is displaying with the respective data of that selected quote and version.

below steps are followed:

created a copy from product table to achieve the requirement.

1. Create new table based on product table and use new table to build visual.

Table formula:

Table = product_data

2.  measure to get selected value from new table, then compare table 2 row contents with above value to return tag.

 Measure:

Tag =
   IF (
         MAX ( addon_data[version] ) IN ALLSELECTED ( 'Table'[version] )
        && MAX ( addon_data[quoteNo] ) IN ALLSELECTED ( 'Table'[quoteNo] ),
         1,
         0
     )

3. Drag tag measure to table 2 visual level filter. 

Requirement -

i  need help to add below condition to the measure:

If i select any row in product_data table... table 2 table is displaying with the respective data of that selected quote and version    Along with this, I also need the proposalId column value in table 2 to be replaced with proposalNewId column value (i.e. 12 ).    fyi... proposalNewId column is in addon_data table