Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi all
i have one question about some condition to add to display N results and N-1 ( N her = Stage)
If for example on table dash board it's the results is for Stage = Stage1, Stage 2, Stage 3 and Stage 4 And it's asked to make to table results
Table 1 for STAGE
and
Table 2 Value of STAGE - 1
For example if we need Table Stage 4 results it will have Table 1 : STAGE 4 And Table 2 : STAGE 3 = STAGE -1 ( precedent one it's like Year and Year-1
Thanks for Help
You can find Power Bi project her thanks for help
Solved! Go to Solution.
Hi @POWER_MI ,
The issue about the blank row is on the Calculation B2 on the table where you have the levels they are blanked out:
Having this will imply to change the calculation formula.
On the formula below I just show the B2 calculation where you have blanks so if on this specific line you have several R that is what is presented doesn't matter what you write in the column on the disconnected table.
SELECTEDVALUE( 'Table'[Level] ) = "B2"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN { "R2"; "R22"; "R31"; "R21" })
If you don't want to present any R you then need to redo this part of the measure to:
SELECTEDVALUE( 'Table'[Level] ) = "B2"; BLANK()
With this the line will disappear.
Be aware that this solution is not dinamic so when you change the basis of the different levels , chaging R from one line to the other you need to update the Calculation measure accordingly.
Regarding the second question I'm not understanding.
You asked fopr one table to have the values from the slicer and the other for the slicer -1 on my tests the results where giving the correct result, maybe I have not understood what you want.
Can you please elaborate what is the exact error you are getting? Do you want to have on table the stage selected and on the second table the stage selected + previous stage?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thanks to answser
Before version or precedent version
If for example
Stage is not caractere but number 1,2,3,4 how to find last step ?
Hi @MFelix
thanks for feed back.
If for example
Stage is not caractere but number 1,2,3,4 how to find last step ?
Hi @POWER_MI ,
What is the purpose you need? the syntax seems fine. But what do you want to achieve?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Hi @MFelix
Thanks to answser
Before version or precedent version
If for example
Stage is not caractere but number 1,2,3,4 how to find last step ?
Hi All,
Hello @MFelix
Have you seen last email. The project is updated with Stage i Like Number
Before version or precedent version
If for example
Stage is not caractere but number 1,2,3,4 how to find last step ?
Hi @POWER_MI ,
Sorry for the late response add some work related issues.
Can you explain a little bit better your issue, looknig at the code you provide there should be no problem in having letters or numbers if things match in both sides should work colrretly.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe update is :
First one that Table'Stage will be not exist So will used Stage colonne of COLOR_FIELD_TO_ADD table ( stage =1,2,3,4 and not stage 1; stage 2 ...
And also if we replace Stage 1, Stage 2 ... byt integer 1,2,3 it's not work.
Hi @POWER_MI
That has to do with the fact that the stage is coming from the same table has your filter so the previous stage is not being pickup on the values you need. You need to do it in a separated table.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thanks for your answer. You need to say that when we have for example on Table A, Step coloumn
so we can not define
Current Step Results and Precedent Step results ?
because on this updates is same example Stage will be not on other table but on data table with value and will be 1,2,3,4
thanks
Hi @POWER_MI ,
This is the exact same thing has I explained in my previous post with the stage text, if you create a disconnected table to use has a slicer everything will work properly.
What is hapenning here is that you are using a slicer to filter a table then you are trying in the same data picking up a subset of you data in your case you are picking up all the stage 4 data. When you then with the same slicer try to get the subset of previous stage that information is no within the subset so no result. However due to context the total values in the matrix are getting pickup because it's based on a different context for the all table.
In this case if you use the same measures it will work correctly:
current stage Stage =
CALCULATE (
[Calculation];
FILTER (
ALL ( COLOR_FIELD_TO_ADD[Stage] );
COLOR_FIELD_TO_ADD[Stage] = SELECTEDVALUE(Stage[Stage]))
)
previous stage Stage =
VAR previuos_selection =
SELECTEDVALUE ( Stage[Stage]) - 1
RETURN
CALCULATE (
[Calculation];
FILTER (
ALL ( COLOR_FIELD_TO_ADD[Stage] );
COLOR_FIELD_TO_ADD[Stage] = previuos_selection)
)
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@POWER_MI , Create stage rank like this
new column
Stage Rank = SWITCH(TRUE(), COLOR_FIELD_TO_ADD[STEP] in {1,2,3}, 1,
COLOR_FIELD_TO_ADD[STEP] in {4}, 2,
COLOR_FIELD_TO_ADD[STEP] in {5}, 3,
COLOR_FIELD_TO_ADD[STEP] in {6,7}, 4
)
this stage = calculate( [measure], filter(all(COLOR_FIELD_TO_ADD), COLOR_FIELD_TO_ADD[Stage Rank] = max(COLOR_FIELD_TO_ADD[Stage Rank] )))
last stage = calculate( [measure], filter(all(COLOR_FIELD_TO_ADD), COLOR_FIELD_TO_ADD[Stage Rank] = max(COLOR_FIELD_TO_ADD[Stage Rank] )-1))
Try measure like
Hi all,
@amitchandak thanks for your answer
The update is done on project but it's not working
seen enclosed projet
In this Stage there are [mesure]? which one ? on enclosed projet it's taken like Mesure1 count rows...
this stage = calculate( [measure], filter(all(COLOR_FIELD_TO_ADD), COLOR_FIELD_TO_ADD[Stage Rank] = max(COLOR_FIELD_TO_ADD[Stage Rank] )))
You can find project here : donwload
Hi @POWER_MI ,
Createa dimension table with the stages (disconnected) now add the following two measures:
current stage Stage =
CALCULATE (
[Calculation];
FILTER (
ALL ( COLOR_FIELD_TO_ADD[Stage] );
COLOR_FIELD_TO_ADD[Stage] = SELECTEDVALUE ( Stages[Stage] )
)
)
previous stage Stage =
VAR previuos_selection =
SELECTEDVALUE ( Stages[Id] ) - 1
VAR Previouse_stage =
MAXX (
FILTER ( ALL ( Stages ); Stages[Id] = previuos_selection );
Stages[Stage]
)
RETURN
CALCULATE (
[Calculation];
FILTER (
ALL ( COLOR_FIELD_TO_ADD[Stage] );
COLOR_FIELD_TO_ADD[Stage] = Previouse_stage
)
)
On the attach file the top two tables Ihave forced the filter on the filter pane just for comparision the bottom ones are working with the measures.
The formating you can use the same condittional formatting for both measure has you use the previous one.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshi All,
@MFelix Thanks for solution
there are two remarks
for Update 1 : on the update one you will see that when the table "table have for example one line with empty value on dashboard results the line will not have colonne 1 and there will be results with choice B green color or correct results will be just two line and No line with green color on exmaple " Type = B"
on table data base Line B, B2 is Empty so logicly the results will be also.
update 2 for the stage N and Stage N-1
for example example when you will are on Stage 4 the table displayed will be
Dashboard results group1
- Stage 4
- Stage 3
the mistakes for example when you do
dashboard results Stage 3
Stage 3
Stage 2
the results on Group 1 and 2 are not same for same stage ?
also the count Ri is not correct ?
Enclosed the projet PB Donwload
thanks for help
Hi @POWER_MI ,
The issue about the blank row is on the Calculation B2 on the table where you have the levels they are blanked out:
Having this will imply to change the calculation formula.
On the formula below I just show the B2 calculation where you have blanks so if on this specific line you have several R that is what is presented doesn't matter what you write in the column on the disconnected table.
SELECTEDVALUE( 'Table'[Level] ) = "B2"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN { "R2"; "R22"; "R31"; "R21" })
If you don't want to present any R you then need to redo this part of the measure to:
SELECTEDVALUE( 'Table'[Level] ) = "B2"; BLANK()
With this the line will disappear.
Be aware that this solution is not dinamic so when you change the basis of the different levels , chaging R from one line to the other you need to update the Calculation measure accordingly.
Regarding the second question I'm not understanding.
You asked fopr one table to have the values from the slicer and the other for the slicer -1 on my tests the results where giving the correct result, maybe I have not understood what you want.
Can you please elaborate what is the exact error you are getting? Do you want to have on table the stage selected and on the second table the stage selected + previous stage?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |