Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Experts
Not sure on how to do the following, when ALL is shown on the filter/slicer i want to use measure from Table A and when the user selects a product from filter/slicer i want to use meaure from Table B
Measure from Table A
KPY
PrimaryYears
Measure from Table B
KMYears
PrimaryYears
Solved! Go to Solution.
Hi @Anonymous ,
Sorry but I'm not understanding your phrase.
In the example I send you I only place the 3 measures for comparision., if you take out the other two measure you only bet the red line that get's one or other calculation.
check PBIX file attach if this is the expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Your information is very reduced but based on what I can understand what you need to do is something similar to this:
Measure A or B = IF ( DISTINCTCOUNT ( Table[SlicerColumn] ) = CALCULATE ( DISTINCTCOUNT ( Table[SlicerColumn] ); ALL ( Table[SlicerColumn] ) ); [Measure Table A]; [Measure Table B ] )
Without having more information is dificult to point out a better answer but this should do the trick.
Adjust the measure based on your tables, columns and measure names.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi MFelix
Ill upload a file with some sample data, i have called your measure All Measure and its in table STEMBRANDYEARS
The filter i have highlighted in Blue.
Table A is called KM Year and i want to use measure MYKMYears and if possible also PRIMARYTOOUTCOMEYEARS when all is selected
and
TableB is called STEMBRANDYEARS and i want to use measure KPYears and if poosible from this table PRIMARYTOOUTCOMEYEARS when a product is selected from the highlighted filter.
https://www.dropbox.com/s/80akom988tzyz0k/Test_KPV_1.1.pbix?dl=0
Hi @Anonymous ,
Believe you have upload incorrect file as you can see there is no All Measure and no highlithed filter:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
To what I can see the issue is that this measure is not formated as percentage as the other two.
I have formatted as % and place the 3 measures on the chart and the numbers where matching when making a selection.
Can you please format measure as percentage, place the other two measures on the visual and then make the selection on the filter to see if they are matching?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix
CAnyou kinldy re send bak or upload the pbix file, kinldy please.
Hi @Anonymous,
See file attach, although you don't see the All Measure on the visual because the other surpass it if you over on a single data point you can see on the tooltip that the values are the same depending on the selection for one or the other measure.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI Mfelix
Apologies if i my have confused you. But When All is selected i want the measure from Table KM Years i.e. KM Years and PRIMARYTOOUTCOMEYEARS to be shown in the graph not else. only one line and like wise when a product is selected the measure from STEMBRANDYEARS, then that line only...and the lines for ALL and the individal product should match the red line grph to the left.
Hi @Anonymous ,
Sorry but I'm not understanding your phrase.
In the example I send you I only place the 3 measures for comparision., if you take out the other two measure you only bet the red line that get's one or other calculation.
check PBIX file attach if this is the expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi MFelix
This is not the except result. the except result is the grapht to the left. we can get he expect result if somehow we can use a switch function where when ALL is shown on the slicer we use mesure from
KM Years
MYKMYears and
PRIMARYTOOUTCOMEYEARS
if you add those above to the Axis PRIMARYTOOUTCOMEYEARS and Vales MYKMYears for ALL
give you the result as per graph on the left.
And when the user select a Product like Birmingham Head ReSection Stem" from filter then we replace Value with MYKMYears and Axis with PRIMARYTOOUTCOMEYEARS from the STEMBRANDYEARS table. and this match once again with the graph on the left.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsResult when measure from KM Years are selected.
Results when user selects a product from filter use measure from table STEMBRANDYEAR
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
As refered your situation is refering to context. When you have two measure that you want to place in the same chart they need to be linked in any manner, and the axis of the visual needs to be inline with both measures.
On your case you are making the calculation based on the PRIMARYOUTCOMEYEARS however on both measures you use the column from each table so since you don't have any common field between them when you place it on a chart using one of the columns (in your case the STEMBRANDYEARS the othe measure gives you a straight line since the context isn't found.
What I did was to create a relationship (inactive) between the PRIMARYOUTCOMEYEARS of both tables then made the following changes:
MYKMYears v2 = 1-(CALCULATE( PRODUCT( 'KM Years'[1-e_i/d_i]); FILTER(ALLSELECTED( 'KM Years'[PRIMARYTOOUTCOMEYEARS]); ISONORAFTER('KM Years'[PRIMARYTOOUTCOMEYEARS];MAX('KM Years'[PRIMARYTOOUTCOMEYEARS]);DESC)))) KPYears v2 = 1 - ( CALCULATE ( PRODUCT ( 'STEMBRANDYEARS'[1-e_i/d_i] ); FILTER ( ALLSELECTED ( 'KM Years'[PRIMARYTOOUTCOMEYEARS] ); ISONORAFTER ( 'KM Years'[PRIMARYTOOUTCOMEYEARS]; MAX ( 'KM Years'[PRIMARYTOOUTCOMEYEARS] ); DESC ) ); USERELATIONSHIP ( 'KM Years'[PRIMARYTOOUTCOMEYEARS]; STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] ) ) ) All Measure = IF ( DISTINCTCOUNT ( STEMBRANDYEARS[STEMBRAND] ) = CALCULATE ( DISTINCTCOUNT ( STEMBRANDYEARS[STEMBRAND] ); ALL ( STEMBRANDYEARS[STEMBRAND] ) ); [MYKMYears v2]; [KPYears v2] )
Although I'm getting the correct result between the visuals there are two errors that I keep getting on the KPYears measure:
Believe that this issues are because of the way your data is structured, and the filters used on the that are not matching the context of the calculations.
Can you explain why you have the tables setup as they are?
Check PBI file attach
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |